﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Globalization;
using System.Data.SqlClient;
using System.Collections;
using System.Text;
using System.IO;
using CommonLib2;


/// <summary>
/// clsDBUtility 的摘要描述
/// </summary>
public partial class clsDBUtility
{

    private static clsDBUtility dbUtil;
    private SqlConnection conn;    

    public static clsDBUtility GetInstance(){
        if (dbUtil == null)
            dbUtil = new clsDBUtility();

        return dbUtil;
    }

    public clsDBUtility()
	{
        conn = new SqlConnection(clsCommon.GetConnectionString());
    }


    #region "Login相關"

    //民眾或公所人員登入時取得登入者資訊
    public DataTable CheckLogin(String LoginID, String LoginPwd)
    {
        SqlParameter[] sqlParams = new SqlParameter[3];

        sqlParams[0] = new SqlParameter("@LoginID", SqlDbType.NVarChar, 50);
        sqlParams[0].Value = LoginID;
        sqlParams[1] = new SqlParameter("@LoginPwd", SqlDbType.NVarChar, 50);
        sqlParams[1].Value = LoginPwd;
        sqlParams[2] = new SqlParameter("@LoginEncodePwd", SqlDbType.NVarChar, 400);
        sqlParams[2].Value = clsCommon.Encode(LoginPwd);


        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT * ");
        sb.AppendLine("FROM FD_view_user ");
        sb.AppendLine("WHERE Active='Y' ");
        sb.AppendLine("AND ID=@LoginID ");
        sb.AppendLine("AND ISNULL(PWD,'')=CASE UserType WHEN 'Person' THEN @LoginEncodePwd WHEN 'Employee' THEN @LoginPwd END ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        if (DS != null && DS.Tables.Count > 0 && DS.Tables[0].Rows.Count > 0)
        {
            return DS.Tables[0];
        }
        else
        {
            return null;
        }


    }

    //檢查帳號是否存在
    public Boolean IsIDExist(String LoginID)
    {
        SqlParameter[] sqlParams = new SqlParameter[1];

        sqlParams[0] = new SqlParameter("@LoginID", SqlDbType.NVarChar, 20);
        sqlParams[0].Value = LoginID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT COUNT(*) ");
        sb.AppendLine("FROM FD_view_user ");
        sb.AppendLine("WHERE ID=@LoginID ");

        return Convert.ToInt32(SqlHelper.ExecuteScalar(conn, CommandType.Text, sb.ToString(), sqlParams)) > 0 ? true : false;

    }




    #endregion

    #region "帳號申請相關"

    //檢查身分證是否存在
    public Boolean IsIDCardNoExist(String IDCardNo,string ID)
    {
        SqlParameter[] sqlParams = new SqlParameter[2];

        sqlParams[0] = new SqlParameter("@IDCardNo", SqlDbType.NVarChar, 50);
        sqlParams[0].Value =clsCommon.Encode(IDCardNo.Trim());
        sqlParams[1] = new SqlParameter("@ID", SqlDbType.NVarChar, 20);
        sqlParams[1].Value = ID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT COUNT(*) ");
        sb.AppendLine("FROM FD_view_user ");
        sb.AppendLine("WHERE IDCardNo=@IDCardNo and (ID<>@ID or @ID='') ");

        return Convert.ToInt32(SqlHelper.ExecuteScalar(conn, CommandType.Text, sb.ToString(), sqlParams)) > 0 ? true : false;

    }



    //SELECT 民眾登入者資料
    public DataTable Applier_Select(String LoginID)
    {
        SqlParameter[] sqlParams = new SqlParameter[1];
        sqlParams[0] = new SqlParameter("@LoginID", SqlDbType.NVarChar, 20);
        sqlParams[0].Value = LoginID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT * ");
        sb.AppendLine("FROM FD_Applier ");
        sb.AppendLine("WHERE LoginID = @LoginID ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        if (DS != null && DS.Tables.Count > 0 && DS.Tables[0].Rows.Count > 0)
        {
            return DS.Tables[0];
        }
        else
        {
            return null;
        }

    }

    //INSERT 民眾登入者資料
    public void Applier_Insert(String LoginID, String LoginPWD, String Name, String IDcardNO, String ContactTEL, String ContactMobil, String ContactCity, String ContactArea, String ContactAddress, String Email, String BankName, String BankBranchName, String BankRemittancesCode)
    {
        SqlParameter[] sqlParams = new SqlParameter[13];

        sqlParams[0] = new SqlParameter("@LoginID", SqlDbType.NVarChar, 20);
        sqlParams[0].Value = LoginID;
        sqlParams[1] = new SqlParameter("@LoginPWD", SqlDbType.NVarChar, 200);
        sqlParams[1].Value = clsCommon.Encode(LoginPWD);
        sqlParams[2] = new SqlParameter("@Name", SqlDbType.NVarChar, 200);
        sqlParams[2].Value = clsCommon.Encode(Name);
        sqlParams[3] = new SqlParameter("@IDcardNO", SqlDbType.NVarChar, 100);
        sqlParams[3].Value = clsCommon.Encode(IDcardNO);
        sqlParams[4] = new SqlParameter("@ContactTEL", SqlDbType.NVarChar, 200);
        sqlParams[4].Value = clsCommon.Encode(ContactTEL);
        sqlParams[5] = new SqlParameter("@ContactMobil", SqlDbType.NVarChar, 150);
        sqlParams[5].Value = clsCommon.Encode(ContactMobil);
        sqlParams[6] = new SqlParameter("@ContactCity", SqlDbType.NVarChar, 50);
        sqlParams[6].Value = clsCommon.Encode(ContactCity);
        sqlParams[7] = new SqlParameter("@ContactArea", SqlDbType.NVarChar, 50);
        sqlParams[7].Value = clsCommon.Encode(ContactArea);
        sqlParams[8] = new SqlParameter("@ContactAddress", SqlDbType.NVarChar, 500);
        sqlParams[8].Value = clsCommon.Encode(ContactAddress);
        sqlParams[9] = new SqlParameter("@Email", SqlDbType.NVarChar, 500);
        sqlParams[9].Value = clsCommon.Encode(Email);
        sqlParams[10] = new SqlParameter("@BankName", SqlDbType.NVarChar, 50);
        sqlParams[10].Value = clsCommon.Encode(BankName);
        sqlParams[11] = new SqlParameter("@BankBranchName", SqlDbType.NVarChar, 50);
        sqlParams[11].Value = clsCommon.Encode(BankBranchName);
        sqlParams[12] = new SqlParameter("@BankRemittancesCode", SqlDbType.NVarChar, 50);
        sqlParams[12].Value = clsCommon.Encode(BankRemittancesCode);

        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Insert into FD_Applier(LoginID,LoginPWD,Name,IDcardNO,ContactTEL,ContactMobil,ContactCity,ContactArea,ContactAddress,Email,BankName,BankBranchName,BankRemittancesCode,Enable) ");
        sb.AppendLine(" values( ");
        sb.AppendLine(" @LoginID, ");
        sb.AppendLine(" @LoginPWD, ");
        sb.AppendLine(" @Name, ");
        sb.AppendLine(" @IDcardNO, ");
        sb.AppendLine(" @ContactTEL, ");
        sb.AppendLine(" @ContactMobil, ");
        sb.AppendLine(" @ContactCity, ");
        sb.AppendLine(" @ContactArea, ");
        sb.AppendLine(" @ContactAddress, ");
        sb.AppendLine(" @Email, ");
        sb.AppendLine(" @BankName, ");
        sb.AppendLine(" @BankBranchName, ");
        sb.AppendLine(" @BankRemittancesCode, ");
        sb.AppendLine(" 'Y' ) ");

        SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);

    }

    //UPDATE 民眾登入者資料
    public void Applier_Update(String LoginID, String LoginPWD, String Name, String IDcardNO, String ContactTEL, String ContactMobil, String ContactCity, String ContactArea, String ContactAddress, String Email, string BankName, string BankBranchName, string BankRemittancesCode)
    {
        SqlParameter[] sqlParams = new SqlParameter[13];
        sqlParams[0] = new SqlParameter("@LoginID", SqlDbType.NVarChar, 20);
        sqlParams[0].Value = LoginID;
        sqlParams[1] = new SqlParameter("@LoginPWD", SqlDbType.NVarChar, 200);
        sqlParams[1].Value = clsCommon.Encode(LoginPWD);
        sqlParams[2] = new SqlParameter("@Name", SqlDbType.NVarChar, 200);
        sqlParams[2].Value = clsCommon.Encode(Name);
        sqlParams[3] = new SqlParameter("@IDcardNO", SqlDbType.NVarChar, 100);
        sqlParams[3].Value = clsCommon.Encode(IDcardNO);
        sqlParams[4] = new SqlParameter("@ContactTEL", SqlDbType.NVarChar, 200);
        sqlParams[4].Value = clsCommon.Encode(ContactTEL);
        sqlParams[5] = new SqlParameter("@ContactMobil", SqlDbType.NVarChar, 150);
        sqlParams[5].Value = clsCommon.Encode(ContactMobil);
        sqlParams[6] = new SqlParameter("@ContactCity", SqlDbType.NVarChar, 50);
        sqlParams[6].Value = clsCommon.Encode(ContactCity);
        sqlParams[7] = new SqlParameter("@ContactArea", SqlDbType.NVarChar, 50);
        sqlParams[7].Value = clsCommon.Encode(ContactArea);
        sqlParams[8] = new SqlParameter("@ContactAddress", SqlDbType.NVarChar, 500);
        sqlParams[8].Value = clsCommon.Encode(ContactAddress);
        sqlParams[9] = new SqlParameter("@Email", SqlDbType.NVarChar, 500);
        sqlParams[9].Value = clsCommon.Encode(Email);
        sqlParams[10] = new SqlParameter("@BankName", SqlDbType.NVarChar, 50);
        sqlParams[10].Value = clsCommon.Encode(BankName);
        sqlParams[11] = new SqlParameter("@BankBranchName", SqlDbType.NVarChar, 50);
        sqlParams[11].Value = clsCommon.Encode(BankBranchName);
        sqlParams[12] = new SqlParameter("@BankRemittancesCode", SqlDbType.NVarChar, 50);
        sqlParams[12].Value = clsCommon.Encode(BankRemittancesCode);

        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Update FD_applier Set ");
        sb.AppendLine(" LoginID = @LoginID, ");
        sb.AppendLine(" LoginPWD = @LoginPWD, ");
        sb.AppendLine(" Name = @Name, ");
        sb.AppendLine(" IDcardNO = @IDcardNO, ");
        sb.AppendLine(" ContactTEL = @ContactTEL, ");
        sb.AppendLine(" ContactMobil = @ContactMobil, ");
        sb.AppendLine(" ContactCity = @ContactCity, ");
        sb.AppendLine(" COntactArea = @COntactArea, ");
        sb.AppendLine(" ContactAddress = @ContactAddress, ");
        sb.AppendLine(" Email = @Email, ");
        sb.AppendLine(" BankName = @BankName, ");
        sb.AppendLine(" BankBranchName = @BankBranchName, ");
        sb.AppendLine(" BankRemittancesCode = @BankRemittancesCode ");
        sb.AppendLine(" where LoginID = @LoginID ");

        SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);

    }

    #endregion

    #region "檔案上傳相關"

    //取得孤兒檔案
    public DataTable GetOrphanFilesInfo()
    {
        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" select * from FD_UploadFiles a ");
        sb.AppendLine(" where CreateAt <= DATEADD(day,-2,getdate())");
        sb.AppendLine(" and SUBSTRING(theclass,1,11)='WitnessFile' and theType not in ");
        sb.AppendLine(" ( ");
        sb.AppendLine(" select FormID from FD_casereportForm ");
        //sb.AppendLine(" where ReporterID =  @CreateBy or DisplayReporterID =  @CreateBy ");
        sb.AppendLine(" ) ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), null);

        if (DS != null && DS.Tables.Count > 0 && DS.Tables[0].Rows.Count > 0)
        {
            return DS.Tables[0];
        }
        else
        {
            return null;
        }

    }

    //刪除孤兒檔案
    public void DeleteOrphanFiles()
    {
        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Delete from FD_UploadFiles  ");
        sb.AppendLine(" where CreateAt <= DATEADD(day,-2,getdate())");
        sb.AppendLine(" and SUBSTRING(theclass,1,11)='WitnessFile' and theType not in ");
        sb.AppendLine(" ( ");
        sb.AppendLine(" select FormID from FD_casereportForm  ");
        //sb.AppendLine(" where ReporterID =  @CreateBy or DisplayReporterID =  @CreateBy ");
        sb.AppendLine(" ) ");

        SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), null);
    }

    public DataTable GetReportPhoto(String theType)
    {
        SqlParameter[] sqlParams = new SqlParameter[1];

        sqlParams[0] = new SqlParameter("@theType", SqlDbType.NVarChar, 50);
        sqlParams[0].Value = theType;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" select theClass,FileSaveName,fileName, ");
        sb.AppendLine(" cast(fileSavePath as nvarchar(100))+cast(fileSaveName as nvarchar(100))+'.'+fileType as Path ");
        sb.AppendLine(" from FD_UploadFiles ");
        sb.AppendLine(" where theClass = 'WitnessFile_msword' and theType = @theType ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        if (DS != null && DS.Tables.Count > 0 && DS.Tables[0].Rows.Count > 0)
        {
            return DS.Tables[0];
        }
        else
        {
            return null;
        }
    }

    public DataTable GetReportMedia(String theType)
    {
        SqlParameter[] sqlParams = new SqlParameter[1];

        sqlParams[0] = new SqlParameter("@theType", SqlDbType.NVarChar, 50);
        sqlParams[0].Value = theType;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" select theClass,FileSaveName,fileName, ");
        sb.AppendLine(" cast(fileSavePath as nvarchar(100))+cast(fileSaveName as nvarchar(100))+'.'+fileType as Path,FileDesc ");
        sb.AppendLine(" from FD_UploadFiles ");
        sb.AppendLine(" where theClass = 'WitnessFile_video' and theType = @theType ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        if (DS != null && DS.Tables.Count > 0 && DS.Tables[0].Rows.Count > 0)
        {
            return DS.Tables[0];
        }
        else
        {
            return null;
        }
    }

    //UploadFile_Insert
    public void UploadFile(String theClass, String theType, String FileName, String FileShortName, String FileType, String FileSavePath, String FileSaveName, String FileDesc, String CreateBy)
    {
        SqlParameter[] sqlParams = new SqlParameter[9];

        sqlParams[0] = new SqlParameter("@theClass", SqlDbType.NVarChar, 20);
        sqlParams[0].Value = theClass;
        sqlParams[1] = new SqlParameter("@theType", SqlDbType.NVarChar, 20);
        sqlParams[1].Value = theType;
        sqlParams[2] = new SqlParameter("@FileName", SqlDbType.NVarChar, 200);
        sqlParams[2].Value = FileName;
        sqlParams[3] = new SqlParameter("@FileShortName", SqlDbType.NVarChar, 200);
        sqlParams[3].Value = FileShortName;
        sqlParams[4] = new SqlParameter("@FileType", SqlDbType.NVarChar, 50);
        sqlParams[4].Value = FileType;
        sqlParams[5] = new SqlParameter("@FileSavePath", SqlDbType.NVarChar, 1000);
        sqlParams[5].Value = FileSavePath;
        sqlParams[6] = new SqlParameter("@FileSaveName", SqlDbType.NVarChar, 50);
        sqlParams[6].Value = FileSaveName;
        sqlParams[7] = new SqlParameter("@FileDesc", SqlDbType.NVarChar, 200);
        sqlParams[7].Value = FileDesc;
        sqlParams[8] = new SqlParameter("@CreateBy", SqlDbType.NVarChar, 50);
        sqlParams[8].Value = CreateBy;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Insert into FD_UploadFiles ");
        sb.AppendLine(" values( ");
        sb.AppendLine(" @theClass, ");
        sb.AppendLine(" @theType, ");
        sb.AppendLine(" @FileName, ");
        sb.AppendLine(" @FileShortName, ");
        sb.AppendLine(" @FileType, ");
        sb.AppendLine(" @FileSavePath, ");
        sb.AppendLine(" @FileSaveName, ");
        sb.AppendLine(" @FileDesc, ");
        sb.AppendLine(" @CreateBy, ");
        sb.AppendLine(" getDate(), ");
        sb.AppendLine(" '0' ) ");

        SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);
    }

    //UploadFile_Delete
    public void UploadFile_Delete(String FileSaveName)
    {
        SqlParameter[] sqlParams = new SqlParameter[1];

        sqlParams[0] = new SqlParameter("@FileSaveName", SqlDbType.NVarChar, 50);
        sqlParams[0].Value = FileSaveName;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Delete from FD_UploadFiles where FileSaveName = @FileSaveName ");

        SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);

    }

    ////UploadFileStatus_Update
    //public void UploadFileStatus_Update(String theType)
    //{
    //    SqlParameter[] sqlParams = new SqlParameter[1];

    //    sqlParams[0] = new SqlParameter("@theType", SqlDbType.NVarChar, 50);
    //    sqlParams[0].Value = theType;

    //    StringBuilder sb = new StringBuilder();

    //    sb.AppendLine(" Update FD_UploadFiles  Set Status = '1' Where theType = @theType ");

    //    SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);

    //}

    #endregion

    #region "clsFormClassLastID相關"

    /// <summary>
    /// 取得資料庫現存表單編號
    /// </summary>
    /// <param name="strClass">表單類型</param>
    /// <returns>回傳一個內含一筆結果資料列的DataTable</returns>
    public DataTable GetLastID(string strClass)
    {
        //Query last id corresponding "class"
        string sqlGetLastID = string.Format("SELECT * From FD_FormClassLastID WHERE Class = '{0}'", strClass);
        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sqlGetLastID);

        if (DS != null && DS.Tables.Count > 0 && DS.Tables[0].Rows.Count > 0)
        {
            return DS.Tables[0];
        }
        else
        {
            throw new Exception("Can't GetLastID");
        }

    }

    /// <summary>
    /// Commit目前表單編號並且更新到資料庫
    /// </summary>
    /// <param name="strClass"></param>
    /// <param name="Number"></param>
    public void UpdateLastID(string strClass, string strYM, int Number)
    {
        //Update last id corresponding "class"
        string sqlUpdateLastID = string.Format("UPDATE FD_FormClassLastID SET Number={0} , YM='{1}' WHERE Class='{2}'", Number.ToString(), strYM, strClass);
        SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlUpdateLastID);
    }

    #endregion

    #region "clsFlowWork相關"

    /// <summary>
    /// 取得某關卡的關卡資訊
    /// </summary>
    /// <param name="FlowType">FlowType值</param>
    /// <param name="PhaseId">PhaseId值</param>
    /// <param name="theType">0:新增(找出該FlowType的第一筆資料),1:同意(找下一關),2:退回上一步(找上一關),3:由@FlowType和@PhaseId指定</param>
    /// <returns></returns>
    public DataTable clsFlowWork_GetFlowPhaseData(int FlowType, int PhaseId, int theType)
    {
        SqlParameter[] sqlParams = new SqlParameter[3];

        sqlParams[0] = new SqlParameter("@FlowType", SqlDbType.Int);
        sqlParams[0].Value = FlowType;
        sqlParams[1] = new SqlParameter("@PhaseId", SqlDbType.Int);
        sqlParams[1].Value = PhaseId;
        sqlParams[2] = new SqlParameter("@theType", SqlDbType.Int);
        sqlParams[2].Value = theType;

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, "sp_clsFlowWork_GetFlowPhaseData", sqlParams);

        if (DS != null && DS.Tables.Count > 0 && DS.Tables[0].Rows.Count > 0)
        {
            return DS.Tables[0];
        }
        else
        {
            return null;
        }

    }


    /// <summary>
    /// 插入一筆簽核紀錄到WorkList
    /// </summary>
    /// <param name="ParentId"></param>
    /// <param name="FlowType"></param>
    /// <param name="PhaseId"></param>
    /// <param name="FormID"></param>
    /// <param name="AppEmpId"></param>
    /// <param name="ActAppEmpId"></param>
    /// <param name="AppResult"></param>
    /// <param name="AppComment"></param>
    /// <param name="AppDate"></param>
    /// <param name="FormStatus"></param>
    /// <returns></returns>
    public int clsFlowWork_InsertRecordToWorkList(int ParentId, int FlowType, int PhaseId, string FormID, string AppEmpId, int AppDeptID, string ActAppEmpId, int AppResult, string AppComment, DateTime AppDate, string ToSign)
    {
        SqlParameter[] sqlParams = new SqlParameter[11];

        sqlParams[0] = new SqlParameter("@ParentId", SqlDbType.Int, 50);
        sqlParams[0].Value = ParentId;
        sqlParams[1] = new SqlParameter("@FlowType", SqlDbType.Int, 50);
        sqlParams[1].Value = FlowType;
        sqlParams[2] = new SqlParameter("@PhaseId", SqlDbType.Int, 50);
        sqlParams[2].Value = PhaseId;
        sqlParams[3] = new SqlParameter("@FormID", SqlDbType.VarChar, 50);
        sqlParams[3].Value = FormID;
        sqlParams[4] = new SqlParameter("@AppEmpId", SqlDbType.VarChar, 50);
        sqlParams[4].Value = AppEmpId;
        sqlParams[5] = new SqlParameter("@AppDeptID", SqlDbType.VarChar, 50);
        sqlParams[5].Value = AppDeptID;
        sqlParams[6] = new SqlParameter("@ActAppEmpId", SqlDbType.VarChar, 50);
        sqlParams[6].Value = ActAppEmpId;
        sqlParams[7] = new SqlParameter("@AppResult", SqlDbType.Int);
        sqlParams[7].Value = AppResult;
        sqlParams[8] = new SqlParameter("@AppComment", SqlDbType.VarChar, 50);
        sqlParams[8].Value = AppComment;
        sqlParams[9] = new SqlParameter("@AppDate", SqlDbType.DateTime);

        if (AppDate == DateTime.MinValue)
        {
            sqlParams[9].Value = System.DBNull.Value;
        }
        else
        {
            sqlParams[9].Value = AppDate;
        }

        sqlParams[10] = new SqlParameter("@ToSign", SqlDbType.VarChar, 50);
        sqlParams[10].Value = ToSign;


        return Convert.ToInt32(SqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, "sp_clsFlowWork_InsertRecordToWorkList", sqlParams));

    }


    /// <summary>
    /// 將現階段簽掉
    /// </summary>
    /// <param name="WorkListId"></param>
    /// <param name="FlowType"></param>
    /// <param name="strFlag"></param>
    public void clsFlowWork_SignCurrentPhase(int WorkListId, string ActAppEmpId, int AppResult, string AppComment, DateTime AppDate, string ToSign)
    {
        SqlParameter[] sqlParams = new SqlParameter[6];

        sqlParams[0] = new SqlParameter("@WorkListId", SqlDbType.Int);
        sqlParams[0].Value = WorkListId;
        sqlParams[1] = new SqlParameter("@ActAppEmpId", SqlDbType.VarChar, 10);
        sqlParams[1].Value = ActAppEmpId;
        sqlParams[2] = new SqlParameter("@AppResult", SqlDbType.Int);
        sqlParams[2].Value = AppResult;
        sqlParams[3] = new SqlParameter("@AppComment", SqlDbType.NVarChar, 200);
        sqlParams[3].Value = AppComment;
        sqlParams[4] = new SqlParameter("@AppDate", SqlDbType.DateTime);
        sqlParams[4].Value = AppDate;
        sqlParams[5] = new SqlParameter("@ToSign", SqlDbType.Char, 1);
        sqlParams[5].Value = ToSign;

        SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "sp_clsFlowWork_SignCurrentPhase", sqlParams);

    }



    public void clsFlowWork_UpdateCloseDate(string FormID)
    {
        SqlParameter[] sqlParams = new SqlParameter[1];

        sqlParams[0] = new SqlParameter("@FormID", SqlDbType.VarChar,20);
        sqlParams[0].Value = FormID;
    
        SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "sp_clsFlowWork_UpdateCloseDate", sqlParams);

    }





    




    #endregion


    #region "CaseReportFormCreate"

    public int CheckRepeat(DateTime Date, string Car_ID)
    {
        SqlParameter[] sqlParams = new SqlParameter[2];

        sqlParams[0] = new SqlParameter("@Date", SqlDbType.DateTime);
        sqlParams[0].Value = Date;
        sqlParams[1] = new SqlParameter("@Car_ID", SqlDbType.NVarChar, 50);
        sqlParams[1].Value = Car_ID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT COUNT(FormID) ");
        sb.AppendLine("FROM FD_CaseReportForm A ");
        sb.AppendLine("WHERE A.Date=@Date and A.Car_ID=@Car_ID; ");

        return (int)SqlHelper.ExecuteScalar(conn, CommandType.Text, sb.ToString(), sqlParams);

    }




    #endregion


    #region "CaseReportForm"

    public DataTable GetFormDetailData(string FormID)
    {
        SqlParameter[] sqlParams = new SqlParameter[1];

        sqlParams[0] = new SqlParameter("@FormID", SqlDbType.NVarChar, 20);
        sqlParams[0].Value = FormID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT A.FormStatus,A.ReporterID,B.Email as ReporterEmail,A.DisplayReporterID,A.OrganizationID ");
        sb.AppendLine("FROM FD_CaseReportForm A ");
        sb.AppendLine("left join FD_Applier B on A.ReporterID = B.LoginID");        
        sb.AppendLine("WHERE A.FormID=@FormID; ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        if (DS != null && DS.Tables.Count > 0 && DS.Tables[0].Rows.Count > 0)
        {
            return DS.Tables[0];
        }
        else
        {
            return null;
        }




    }



    public int ChangeFormStatus(int FormStatus,string FormID)
    {
        SqlParameter[] sqlParams = new SqlParameter[3];

        sqlParams[0] = new SqlParameter("@FormStatus", SqlDbType.Int);
        sqlParams[0].Value = FormStatus;
        sqlParams[1] = new SqlParameter("@FormID", SqlDbType.NVarChar,20);
        sqlParams[1].Value = FormID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("UPDATE FD_CaseReportForm ");
        sb.AppendLine("SET FormStatus=@FormStatus ");

        if (FormStatus >= 10)
        {
            sb.AppendLine(",CloseDate=getdate() ");        
        }

        sb.AppendLine("WHERE FormID=@FormID; ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);

    }

    #endregion



    #region "CaseReportForm"

    /// <summary>
    /// 取得某單號下的WorkList資料
    /// </summary>
    /// <param name="strFormID"></param>
    //public DataTable Get_Select_WorkList_ByFormID(string strFormID)
    //{
    //    SqlParameter[] sqlParams = new SqlParameter[1];

    //    sqlParams[0] = new SqlParameter("@FormID", SqlDbType.VarChar, 50);
    //    sqlParams[0].Value = strFormID;

    //    DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, "sp_Select_WorkList_ByFormID", sqlParams);

    //    if (DS != null && DS.Tables.Count > 0 && DS.Tables[0].Rows.Count > 0)
    //    {
    //        return DS.Tables[0];
    //    }
    //    else
    //    {
    //        return null;
    //    }

    //}


    /// <summary>
    /// 某單號下的資料全部確定成立(成立日期設為當下日期)
    /// </summary>
    /// <param name="strFormID"></param>
    //public void SetAllFormCreateDate(string strFormID)
    //{

    //    SqlParameter[] sqlParams = new SqlParameter[1];

    //    sqlParams[0] = new SqlParameter("@FormID", SqlDbType.VarChar, 50);
    //    sqlParams[0].Value = strFormID;

    //    StringBuilder sb = new StringBuilder();

    //    sb.AppendLine("UPDATE FD_CaseReportForm SET CreateAt=getdate() WHERE FormID=@FormID; ");
    //    sb.AppendLine("UPDATE FD_CheckForm SET CreateAt=getdate() WHERE FormID=@FormID; ");
    //    sb.AppendLine("UPDATE FD_ActionForm SET CreateAt=getdate() WHERE FormID=@FormID; ");
    //    sb.AppendLine("UPDATE FD_ReleaseForm SET CreateAt=getdate() WHERE FormID=@FormID; ");

    //    SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);

    //}


    #endregion



    //public DataTable SELECTForSample(string FormID)
    //{
    //    SqlParameter[] sqlParams = new SqlParameter[1];

    //    sqlParams[0] = new SqlParameter("@FormID", SqlDbType.NVarChar, 50);
    //    sqlParams[0].Value = FormID;


    //    StringBuilder sb = new StringBuilder();

    //    sb.AppendLine("SELECT FormID,Date,(B.RegionName + A.Place) as Place,Action,ActionOther,Car_ID ");
    //    sb.AppendLine("FROM FD_caseReportForm A ");
    //    sb.AppendLine("left join FD_view_RegionOrganization B on A.OrganizationID=B.OrganizationID ");
    //    sb.AppendLine("WHERE FormID = @FormID ; ");
    //    sb.AppendLine("SELECT *  ");
    //    sb.AppendLine("FROM FD_CodeMapping  ");
    //    sb.AppendLine("WHERE TableNAme='CaseReportForm' AND [ColumnName]='Action' ");

    //    DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

    //    if (DS != null && DS.Tables.Count > 0 && DS.Tables[0].Rows.Count > 0)
    //    {
    //        string MyAction=DS.Tables[0].Rows[0]["Action"].ToString() + ",";

    //        MyAction = MyAction.Replace("8,", DS.Tables[0].Rows[0]["ActionOther"].ToString() + ",");

    //        foreach (DataRow MyDR in DS.Tables[1].Rows)
    //        {
    //           MyAction = MyAction.Replace((MyDR["Value"] + ","), (MyDR["Text"] + ","));
    //        }

    //        DS.Tables[0].Rows[0]["Action"] = MyAction.Remove(MyAction.Length -1);

    //        return DS.Tables[0];
    //    }
    //    else
    //    {
    //        return null;
    //    }

    //}


    #region "ucCaseReportForm"



    public DataTable ucCaseReportForm_Select(string strFormID)
    {

        SqlParameter[] sqlParams = new SqlParameter[1];

        sqlParams[0] = new SqlParameter("@FormID", SqlDbType.VarChar, 50);
        sqlParams[0].Value = strFormID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT FormID,ReporterID,DisplayReporterID,Date,DATENAME(hour, Date) as Hour,DATENAME(minute, Date) as Minute,OrganizationID,Place,Action,ActionOther,ReportDate,Car_ID ");
        sb.AppendLine("FROM FD_caseReportForm ");
        sb.AppendLine("WHERE FormID = @FormID ; ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        if (DS != null && DS.Tables.Count > 0 && DS.Tables[0].Rows.Count > 0)
        {
            return DS.Tables[0];
        }
        else
        {
            return null;
        }


    }

    public int ucCaseReportForm_Insert(String FormID, String ReporterID, string DisplayReporterID, String Date, String Hour, String Minute, int OrganizationID, String Place, string Car_ID, String Action, String ActionOther, int FormStatus, string ReportDate)
    {

        SqlParameter[] sqlParams = new SqlParameter[11];

        sqlParams[0] = new SqlParameter("@FormID", SqlDbType.NVarChar, 50);
        sqlParams[0].Value = FormID;
        sqlParams[1] = new SqlParameter("@ReporterID", SqlDbType.NVarChar, 50);
        sqlParams[1].Value = ReporterID;
        sqlParams[2] = new SqlParameter("@DisplayReporterID", SqlDbType.NVarChar, 50);
        sqlParams[2].Value = DisplayReporterID;
        sqlParams[3] = new SqlParameter("@Date", SqlDbType.DateTime);

        try
        {
            sqlParams[3].Value = Convert.ToDateTime(string.Format("{0} {1}:{2}:00", Date, Hour, Minute));
        }
        catch (Exception ex)
        {
            sqlParams[3].Value = System.DBNull.Value;
        }

        sqlParams[4] = new SqlParameter("@OrganizationID", SqlDbType.Int);
        sqlParams[4].Value = OrganizationID;
        sqlParams[5] = new SqlParameter("@Place", SqlDbType.NVarChar, 50);
        sqlParams[5].Value = Place;
        sqlParams[6] = new SqlParameter("@Car_ID", SqlDbType.NVarChar, 50);
        sqlParams[6].Value = Car_ID;
        sqlParams[7] = new SqlParameter("@Action", SqlDbType.NVarChar, 50);
        sqlParams[7].Value = Action;
        sqlParams[8] = new SqlParameter("@ActionOther", SqlDbType.NVarChar, 50);
        sqlParams[8].Value = ActionOther;
        sqlParams[9] = new SqlParameter("@FormStatus", SqlDbType.Int);
        sqlParams[9].Value = FormStatus;
        
        sqlParams[10] = new SqlParameter("@ReportDate", SqlDbType.DateTime);

        if (ReportDate == null)
        {
            sqlParams[10].Value = System.DBNull.Value;
        }
        else
        {
            sqlParams[10].Value = ReportDate;
        }

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("INSERT FD_CaseReportForm (FormID,ReporterID,DisplayReporterID,[Date],OrganizationID,Place,Car_ID,[Action],ActionOther,FormStatus,ReportDate,RejectReson,Car_OrgType,Car_Type) ");
        sb.AppendLine("SELECT @FormID,@ReporterID,@DisplayReporterID,@Date,@OrganizationID,@Place,@Car_ID,@Action,@ActionOther,@FormStatus,@ReportDate,0,0,0; ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);

    }

    public int ucCaseReportForm_Update(String original_FormID, String Date, String Hour, String Minute, int OrganizationID, String Place, string Car_ID, String Action, String ActionOther, int FormStatus, string ReportDate)
    {

        SqlParameter[] sqlParams = new SqlParameter[9];

        sqlParams[0] = new SqlParameter("@original_FormID", SqlDbType.NVarChar, 50);
        sqlParams[0].Value = original_FormID;
        sqlParams[1] = new SqlParameter("@Date", SqlDbType.DateTime);

        DateTime theDate;
        if (DateTime.TryParse(string.Format("{0} {1}:{2}:00", Date, Hour, Minute), out theDate))
        {
            sqlParams[1].Value = theDate;
        }
        else
        {
            sqlParams[1].Value = System.DBNull.Value;
        }


        sqlParams[2] = new SqlParameter("@OrganizationID", SqlDbType.Int);
        sqlParams[2].Value = OrganizationID;
        sqlParams[3] = new SqlParameter("@Place", SqlDbType.NVarChar, 50);
        sqlParams[3].Value = Place;
        sqlParams[4] = new SqlParameter("@Car_ID", SqlDbType.NVarChar, 50);
        sqlParams[4].Value = Car_ID;
        sqlParams[5] = new SqlParameter("@Action", SqlDbType.NVarChar, 50);
        sqlParams[5].Value = Action;
        sqlParams[6] = new SqlParameter("@ActionOther", SqlDbType.NVarChar, 50);
        sqlParams[6].Value = ActionOther;
        sqlParams[7] = new SqlParameter("@FormStatus", SqlDbType.Int);
        sqlParams[7].Value = FormStatus;

        sqlParams[8] = new SqlParameter("@ReportDate", SqlDbType.DateTime);

        if (ReportDate == null)
        {
            sqlParams[8].Value = System.DBNull.Value;
        }
        else
        {
            sqlParams[8].Value = ReportDate;
        }

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("UPDATE FD_CaseReportForm ");
        sb.AppendLine("SET [Date]=@Date,OrganizationID=@OrganizationID,Place=@Place,Car_ID=@Car_ID,[Action]=@Action,ActionOther=@ActionOther,FormStatus=@FormStatus,ReportDate=@ReportDate ");
        sb.AppendLine("WHERE FormID=@original_FormID; ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);

    }


    #endregion

    //public DataTable ucCaseReportForm_Select_Organization()
    //{

    //    StringBuilder sb = new StringBuilder();

    //    sb.AppendLine("SELECT * FROM FD_view_RegionOrganization ");

    //    DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), null);

    //    if (DS != null && DS.Tables.Count > 0)
    //    {
    //        return DS.Tables[0];
    //    }
    //    else
    //    {
    //        return null;
    //    }

    //}

    public int ucCaseReportForm_UpdatePetitionResult(String original_FormID, String PetitionResult)
    {

        SqlParameter[] sqlParams = new SqlParameter[2];

        sqlParams[0] = new SqlParameter("@original_FormID", SqlDbType.NVarChar, 50);
        sqlParams[0].Value = original_FormID;
        sqlParams[1] = new SqlParameter("@PetitionResult", SqlDbType.Int);
        sqlParams[1].Value = PetitionResult;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("UPDATE FD_CaseReportForm ");
        sb.AppendLine("SET PetitionResult=@PetitionResult ");
        sb.AppendLine("WHERE FormID=@original_FormID; ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);
    }

   

    #region "ucCaseReportForm_OpenChooseApplier"
    public DataTable ucCaseReportForm_Select()
    {

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT LoginID,Name,IDcardNO ");
        sb.AppendLine("FROM FD_Applier ");
        sb.AppendLine("WHERE Enable = 'Y' ; ");

        DataTable DT = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), null).Tables[0];

        foreach (DataRow DR in DT.Rows )
        {
            DR[1] = clsCommon.Decode(DR[1].ToString());
            DR[2] = clsCommon.Decode(DR[2].ToString());
        }

        return DT;

    }

    #endregion    


    //資料審查階段
    #region "ucConfirmForm.ascx"

    //SELECT
    public DataTable ucConfirmForm_Select(string FormID)
    {

        SqlParameter[] sqlParams = new SqlParameter[1];

        sqlParams[0] = new SqlParameter("@FormID", SqlDbType.NVarChar, 20);
        sqlParams[0].Value = FormID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT FormID,ConfirmResult,RejectReson,RejectRemark FROM FD_CaseReportForm WHERE FormID=@FormID ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        if (DS != null && DS.Tables.Count > 0)
        {
            return DS.Tables[0];
        }
        else
        {
            return null;
        }

    }

    //UPDATE
    public int ucConfirmForm_Update(String original_FormID, int? ConfirmResult, int RejectReson, string RejectRemark)
    {

        SqlParameter[] sqlParams = new SqlParameter[4];

        sqlParams[0] = new SqlParameter("@FormID", SqlDbType.NVarChar, 20);
        sqlParams[0].Value = original_FormID;
        sqlParams[1] = new SqlParameter("@ConfirmResult", SqlDbType.Int);
        sqlParams[1].Value = ConfirmResult;
        sqlParams[2] = new SqlParameter("@RejectReson", SqlDbType.Int);
        sqlParams[2].Value = RejectReson;
        sqlParams[3] = new SqlParameter("@RejectRemark", SqlDbType.NVarChar,500);
        sqlParams[3].Value = RejectRemark;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("UPDATE FD_CaseReportForm ");
        sb.AppendLine("SET ConfirmResult=@ConfirmResult,RejectReson=@RejectReson,RejectRemark=@RejectRemark ");
        sb.AppendLine("WHERE FormID=@FormID; ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);

    }

 
    //違規判定的資料來源
    public DataTable ucConfirmForm_SelectRejectResonDS()
    {
        return Get_CodeMapping("CaseReportForm", "RejectReson");
    }

    #endregion

    #region "ucCheckForm.ascx"

    //SELECT
    public DataTable ucCheckForm_Select(string FormID)
    {

        SqlParameter[] sqlParams = new SqlParameter[1];

        sqlParams[0] = new SqlParameter("@FormID", SqlDbType.NVarChar, 20);
        sqlParams[0].Value = FormID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT FormID,Car_ControlID,Car_OrgType,Car_ChargePerson,Car_ID,Car_Type,CheckResult,CheckReason,CheckRemark FROM FD_CaseReportForm WHERE FormID=@FormID ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        if (DS != null && DS.Tables.Count > 0)
        {
            return DS.Tables[0];
        }
        else
        {
            return null;
        }

    }

    //UPDATE
    public int ucCheckForm_Update(String original_FormID, string Car_ControlID, int Car_OrgType, string Car_ChargePerson, string Car_ID, string Car_Type,int CheckResult,int CheckReason,string CheckRemark)
    {

        SqlParameter[] sqlParams = new SqlParameter[9];

        sqlParams[0] = new SqlParameter("@FormID", SqlDbType.NVarChar, 20);
        sqlParams[0].Value = original_FormID;
        sqlParams[1] = new SqlParameter("@Car_ControlID", SqlDbType.NVarChar, 50);
        sqlParams[1].Value = Car_ControlID;
        sqlParams[2] = new SqlParameter("@Car_OrgType", SqlDbType.Int);
        sqlParams[2].Value = Car_OrgType;
        sqlParams[3] = new SqlParameter("@Car_ChargePerson", SqlDbType.NVarChar, 50);
        sqlParams[3].Value = Car_ChargePerson;
        sqlParams[4] = new SqlParameter("@Car_ID", SqlDbType.NVarChar, 50);
        sqlParams[4].Value = Car_ID;
        sqlParams[5] = new SqlParameter("@Car_Type", SqlDbType.NVarChar, 50);
        sqlParams[5].Value = Car_Type;

        sqlParams[6] = new SqlParameter("@CheckResult", SqlDbType.Int);
        sqlParams[6].Value = CheckResult;
        sqlParams[7] = new SqlParameter("@CheckReason", SqlDbType.Int);
        sqlParams[7].Value = CheckReason;
        sqlParams[8] = new SqlParameter("@CheckRemark", SqlDbType.NVarChar, 500);
        sqlParams[8].Value = CheckRemark;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("UPDATE FD_CaseReportForm ");
        sb.AppendLine("SET Car_ControlID=@Car_ControlID,Car_OrgType=@Car_OrgType,Car_ChargePerson=@Car_ChargePerson,Car_ID=@Car_ID,Car_Type=@Car_Type,CheckResult=@CheckResult,CheckReason=@CheckReason,CheckRemark=@CheckRemark ");
        sb.AppendLine("WHERE FormID=@FormID; ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);

    }

    //組織類型的資料來源
    public DataTable ucCheckForm_SelectCarOrgTypeDS()
    {
        return Get_CodeMapping("CaseReportForm", "CarOrgType");
    }

    //違規判定的資料來源
    public DataTable ucCheckForm_SelectRejectResonDS()
    {
        return Get_CodeMapping("CaseReportForm", "RejectReson");
    }

    //車型的資料來源
    public DataTable ucCheckForm_SelectCarTypeDS()
    {
        return Get_CodeMapping("CaseReportForm", "CarType");
    }


    //Address1的資料來源
    public DataTable ucCheckForm_SelectAddress1DS()
    {

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT RegionID,RegionName ");
        sb.AppendLine("FROM FD_view_Region_TW ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), null);

        if (DS != null && DS.Tables.Count > 0)
        {
            DataRow dr = DS.Tables[0].NewRow();
            dr[0] = 0;
            dr[1] = "請選擇";

            DS.Tables[0].Rows.InsertAt(dr, 0);
            return DS.Tables[0];
        }
        else
        {
            return null;
        }


    }

    //Address2的資料來源
    public DataTable ucCheckForm_SelectAddress2DS(string Parent)
    {
        SqlParameter[] sqlParams = new SqlParameter[1];

        sqlParams[0] = new SqlParameter("@Parent", SqlDbType.NVarChar, 50);
        sqlParams[0].Value = Parent;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT id,RegionName ");
        sb.AppendLine("FROM FD_ZIPCode ");
        sb.AppendLine("WHERE Parent=@Parent ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        if (DS != null && DS.Tables.Count > 0)
        {
            DataRow dr = DS.Tables[0].NewRow();
            dr[0] = 0;
            dr[1] = "請選擇";

            DS.Tables[0].Rows.InsertAt(dr, 0);
            return DS.Tables[0];
        }
        else
        {
            return null;
        }
    }

    //CheckForm_DescDoc DescResult(陳述結果)的資料來源
    public DataTable ucCheckForm_SelectCheckReasonADS()
    {
        return Get_CodeMapping("CaseReportForm_DescDoc", "CheckReasonA");
    }

    //CheckForm_DescDoc DescResult(陳述結果)的資料來源
    public DataTable ucCheckForm_SelectCheckReasonBDS()
    {
        return Get_CodeMapping("CaseReportForm_DescDoc", "CheckReasonB");
    }
    
    #endregion

    #region "ucCheckForm_CarOwner"

    //SELECT
    public DataTable ucCheckForm_CarOwner_Select(string FormID)
    {
        DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtCaseReportForm_CarOwner;

        if (MyDataTable == null)
        {
            SqlParameter[] sqlParams = new SqlParameter[1];

            sqlParams[0] = new SqlParameter("@FormID", SqlDbType.NVarChar, 20);
            sqlParams[0].Value = FormID;

            StringBuilder sb = new StringBuilder();

            sb.AppendLine("SELECT * FROM FD_CaseReportForm_CarOwner WHERE FormID=@FormID ORDER BY id DESC");

            DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

            DS.Tables[0].Columns[1].AutoIncrement = true;

            if (DS != null && DS.Tables.Count > 0)
            {
                MyDataTable = DS.Tables[0];
            }
            else
            {
                MyDataTable = null;
            }
        }

        ((clsPageBase)HttpContext.Current.Handler).dtCaseReportForm_CarOwner = MyDataTable;

        return MyDataTable;

    }

    //INSERT
    public int ucCheckForm_CarOwner_Insert(String FormID, String Car_OwnerName, String Car_OwnerID, String Car_ContactAddress1, String Car_ContactAddress2, string Car_ContactAddress3, string Car_OriginAddress1, string Car_OriginAddress2, string Car_OriginAddress3)
    {
        int iResult = 0;

        try
        {
            DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtCaseReportForm_CarOwner;

            if (MyDataTable == null)
            {
                MyDataTable = ucCheckForm_CarOwner_Select(string.Empty);
            }
            else
            {
                //檢查資料列是否已存在
                if (MyDataTable.Select(string.Format("Car_OwnerID='{0}'", Car_OwnerID)).Length > 0)
                {
                    throw new Exception(string.Format("行為人(相對人)統編 [{0}]已存在!", Car_OwnerID));
                }
            }

            DataRow drNewRow = MyDataTable.NewRow();

            drNewRow["FormID"] = FormID;
            drNewRow["Car_OwnerName"] = Car_OwnerName;
            drNewRow["Car_OwnerID"] = Car_OwnerID;
            drNewRow["Car_ContactAddress1"] = Car_ContactAddress1;
            drNewRow["Car_ContactAddress2"] = Car_ContactAddress2;
            drNewRow["Car_ContactAddress3"] = Car_ContactAddress3;
            drNewRow["Car_OriginAddress1"] = Car_OriginAddress1;
            drNewRow["Car_OriginAddress2"] = Car_OriginAddress2;
            drNewRow["Car_OriginAddress3"] = Car_OriginAddress3;

            MyDataTable.Rows.Add(drNewRow);

            iResult = 1;
            return iResult;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

    }

    //UPDATE
    public int ucCheckForm_CarOwner_Update(int original_id, String Car_OwnerName, String Car_OwnerID, String Car_ContactAddress1, String Car_ContactAddress2, string Car_ContactAddress3, string Car_OriginAddress1, string Car_OriginAddress2, string Car_OriginAddress3)
    {
        int iResult = 0;

        try
        {
            DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtCaseReportForm_CarOwner;

            if (MyDataTable == null)
            {
                throw new Exception("無法Update:dtCaseReportForm_CarOwner不存在!");
            }

            DataRow[] drRows = MyDataTable.Select(string.Format("id={0}", original_id));

            drRows[0]["Car_OwnerName"] = Car_OwnerName;
            drRows[0]["Car_OwnerID"] = Car_OwnerID;
            drRows[0]["Car_ContactAddress1"] = Car_ContactAddress1;
            drRows[0]["Car_ContactAddress2"] = Car_ContactAddress2;
            drRows[0]["Car_ContactAddress3"] = Car_ContactAddress3;
            drRows[0]["Car_OriginAddress1"] = Car_OriginAddress1;
            drRows[0]["Car_OriginAddress2"] = Car_OriginAddress2;
            drRows[0]["Car_OriginAddress3"] = Car_OriginAddress3;

            iResult = 1;

            return iResult;

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

    }

    //DELETE
    public int ucCheckForm_CarOwner_Delete(int original_id)
    {
        int iResult = 0;

        try
        {
            DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtCaseReportForm_CarOwner;

            foreach (DataRow objDataRow in MyDataTable.Select(string.Format("id={0}", original_id)))
            {
                objDataRow.Delete();
                iResult++;
            }

            if (iResult != 1)
            {
                throw new Exception("Delete筆數不為1:" + iResult.ToString());
            }

            return iResult;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

    }

    #endregion

    #region "ucCheckForm_DescDocNotice"

    //SELECT
    public DataTable ucCheckForm_DescDocNotice_Select(string FormID)
    {
        DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtCaseReportForm_DescDocNotice;

        if (MyDataTable == null)
        {
            SqlParameter[] sqlParams = new SqlParameter[1];

            sqlParams[0] = new SqlParameter("@FormID", SqlDbType.NVarChar, 20);
            sqlParams[0].Value = FormID;

            StringBuilder sb = new StringBuilder();

            sb.AppendLine("SELECT * ");
            sb.AppendLine("FROM FD_CaseReportForm_DescDocNotice A ");
            sb.AppendLine("WHERE A.FormID=@FormID "); 

            DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

            DS.Tables[0].Columns[0].AutoIncrement = true;

            if (DS != null && DS.Tables.Count > 0)
            {
                MyDataTable = DS.Tables[0];
            }
            else
            {
                MyDataTable = null;
            }
        }

        ((clsPageBase)HttpContext.Current.Handler).dtCaseReportForm_DescDocNotice = MyDataTable;

        return MyDataTable;

    }

    //INSERT
    public int ucCheckForm_DescDocNotice_Insert(String FormID,string Car_OwnerID, String NoticeID, String NoticeDate, String NoticeDeliverDate, String ReturnBefore)
    {
        int iResult = 0;

        try
        {
            DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtCaseReportForm_DescDocNotice;

            if (MyDataTable == null)
            {
                MyDataTable = ucCheckForm_DescDocNotice_Select(string.Empty);
            }
            else
            {
                //檢查資料列是否已存在
                if (MyDataTable.Select(string.Format("NoticeID='{0}'", NoticeID)).Length > 0)
                {
                    throw new Exception(string.Format("陳述意見通知文號[{0}]已存在!", NoticeID));
                }
            }

            DataRow drNewRow = MyDataTable.NewRow();

            drNewRow["FormID"] = FormID;
            drNewRow["Car_OwnerID"] = Car_OwnerID;
            drNewRow["NoticeID"] = NoticeID;

            if (NoticeDate.Trim() == string.Empty)
            {
                drNewRow["NoticeDate"] = System.DBNull.Value;
            }
            else
            {
                drNewRow["NoticeDate"] = Convert.ToDateTime(NoticeDate);
            }

            if (NoticeDeliverDate.Trim() == string.Empty)
            {
                drNewRow["NoticeDeliverDate"] = System.DBNull.Value;
            }
            else
            {
                drNewRow["NoticeDeliverDate"] = Convert.ToDateTime(NoticeDeliverDate);
            }

            if (ReturnBefore.Trim() == string.Empty)
            {
                drNewRow["ReturnBefore"] = System.DBNull.Value;
            }
            else
            {
                drNewRow["ReturnBefore"] = Convert.ToDateTime(ReturnBefore);
            }

            MyDataTable.Rows.Add(drNewRow);

            iResult = 1;
            return iResult;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

    }
    
    //UPDATE
    public int ucCheckForm_DescDocNotice_Update(int original_id, string Car_OwnerID, String NoticeID, String NoticeDate, String NoticeDeliverDate, String ReturnBefore)
    {
        int iResult = 0;

        try
        {
            DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtCaseReportForm_DescDocNotice;

            if (MyDataTable == null)
            {
                throw new Exception("無法Update:dtCaseReportForm_DescDocNotice不存在!");
            }

            DataRow[] drRows = MyDataTable.Select(string.Format("id={0}", original_id));

            drRows[0]["Car_OwnerID"] = Car_OwnerID;
            drRows[0]["NoticeID"] = NoticeID;

            if (NoticeDate.Trim() == string.Empty)
            {
                drRows[0]["NoticeDate"] = System.DBNull.Value;
            }
            else
            {
                drRows[0]["NoticeDate"] = Convert.ToDateTime(NoticeDate);
            }

            if (NoticeDeliverDate.Trim() == string.Empty)
            {
                drRows[0]["NoticeDeliverDate"] = System.DBNull.Value;
            }
            else
            {
                drRows[0]["NoticeDeliverDate"] = Convert.ToDateTime(NoticeDeliverDate);
            }

            if (ReturnBefore.Trim() == string.Empty)
            {
                drRows[0]["ReturnBefore"] = System.DBNull.Value;
            }
            else
            {
                drRows[0]["ReturnBefore"] = Convert.ToDateTime(ReturnBefore);
            }

            iResult = 1;

            return iResult;

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

    }

    //DELETE
    public int ucCheckForm_DescDocNotice_Delete(int original_id)
    {
        int iResult = 0;

        try
        {
            DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtCaseReportForm_DescDocNotice;

            foreach (DataRow objDataRow in MyDataTable.Select(string.Format("id={0}", original_id)))
            {
                objDataRow.Delete();
                iResult++;
            }

            if (iResult != 1)
            {
                throw new Exception("Delete筆數不為1:" + iResult.ToString());
            }

            return iResult;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

    }

    #endregion

    #region "ucCheckForm_DescDoc"

    //SELECT
    public DataTable ucCheckForm_DescDoc_Select(string FormID)
    {
        DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtCaseReportForm_DescDoc;

        if (MyDataTable == null)
        {
            SqlParameter[] sqlParams = new SqlParameter[1];

            sqlParams[0] = new SqlParameter("@FormID", SqlDbType.NVarChar, 20);
            sqlParams[0].Value = FormID;

            StringBuilder sb = new StringBuilder();

            sb.AppendLine("SELECT * FROM FD_CaseReportForm_DescDoc WHERE FormID=@FormID ");

            DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

            DS.Tables[0].Columns[0].AutoIncrement = true;

            if (DS != null && DS.Tables.Count > 0)
            {
                MyDataTable = DS.Tables[0];
            }
            else
            {
                MyDataTable = null;
            }
        }

        ((clsPageBase)HttpContext.Current.Handler).dtCaseReportForm_DescDoc = MyDataTable;

        return MyDataTable;

    }

    //INSERT
    public int ucCheckForm_DescDoc_Insert(String FormID, string Car_OwnerID, String DescID, String DescDate, int DescResult, String DescContent)
    {
        int iResult = 0;

        try
        {
            DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtCaseReportForm_DescDoc;

            if (MyDataTable == null)
            {
                MyDataTable = ucCheckForm_DescDoc_Select(string.Empty);
            }
            else
            {
                //檢查資料列是否已存在
                if (MyDataTable.Select(string.Format("DescID='{0}'", DescID)).Length > 0)
                {
                    throw new Exception(string.Format("行為人(相對人)回覆意見文號[{0}]已存在!", DescID));
                }
            }

            DataRow drNewRow = MyDataTable.NewRow();

            drNewRow["FormID"] = FormID;
            drNewRow["Car_OwnerID"] = Car_OwnerID;
            drNewRow["DescID"] = DescID;

            if (DescDate.Trim() == string.Empty)
            {
                drNewRow["DescDate"] = System.DBNull.Value;
            }
            else
            {
                drNewRow["DescDate"] = Convert.ToDateTime(DescDate);
            }

            drNewRow["DescResult"] = DescResult;
            drNewRow["DescContent"] = DescContent;

            MyDataTable.Rows.Add(drNewRow);

            iResult = 1;
            return iResult;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

    }

    //UPDATE
    public int ucCheckForm_DescDoc_Update(int original_id, string Car_OwnerID, String DescID, String DescDate, int DescResult, String DescContent)
    {
        int iResult = 0;

        try
        {
            DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtCaseReportForm_DescDoc;

            if (MyDataTable == null)
            {
                throw new Exception("無法Update:dtCaseReportForm_DescDoc不存在!");
            }

            DataRow[] drRows = MyDataTable.Select(string.Format("id={0}", original_id));

            drRows[0]["Car_OwnerID"] = Car_OwnerID;
            drRows[0]["DescID"] = DescID;

            if (DescDate.Trim() == string.Empty)
            {
                drRows[0]["DescDate"] = System.DBNull.Value;
            }
            else
            {
                drRows[0]["DescDate"] = Convert.ToDateTime(DescDate);
            }

            drRows[0]["DescResult"] = DescResult;
            drRows[0]["DescContent"] = DescContent;

            iResult = 1;

            return iResult;

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

    }

    //DELETE
    public int ucCheckForm_DescDoc_Delete(int original_id)
    {
        int iResult = 0;

        try
        {
            DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtCaseReportForm_DescDoc;

            foreach (DataRow objDataRow in MyDataTable.Select(string.Format("id={0}", original_id)))
            {
                objDataRow.Delete();
                iResult++;
            }

            if (iResult != 1)
            {
                throw new Exception("Delete筆數不為1:" + iResult.ToString());
            }

            return iResult;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

    }

    #endregion


    #region "ucActionForm.ascx"

    //XX資料來源
    public DataTable ucActionForm_SelectPetitionResult()
    {
        return Get_CodeMapping("CaseReportForm", "PetitionResult");
    }

    public DataTable ucActionForm_Select(string strFormID)
    {

        SqlParameter[] sqlParams = new SqlParameter[1];

        sqlParams[0] = new SqlParameter("@FormID", SqlDbType.VarChar, 50);
        sqlParams[0].Value = strFormID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT FormID,PetitionResult,PetitionRemark ");
        sb.AppendLine("FROM FD_caseReportForm ");
        sb.AppendLine("WHERE FormID = @FormID ; ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        if (DS != null && DS.Tables.Count > 0)
        {
            return DS.Tables[0];
        }
        else
        {
            return null;
        }


    }

    public int ucActionForm_Update(String original_FormID, int PetitionResult, String PetitionRemark)
    {

        SqlParameter[] sqlParams = new SqlParameter[3];

        sqlParams[0] = new SqlParameter("@FormID", SqlDbType.NVarChar, 50);
        sqlParams[0].Value = original_FormID;
        sqlParams[1] = new SqlParameter("@PetitionResult", SqlDbType.Int);
        sqlParams[1].Value = PetitionResult;
        sqlParams[2] = new SqlParameter("@PetitionRemark", SqlDbType.NVarChar,500);
        sqlParams[2].Value = PetitionRemark;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("UPDATE FD_CaseReportForm ");
        sb.AppendLine("SET PetitionResult=@PetitionResult, ");
        sb.AppendLine("PetitionRemark=@PetitionRemark ");
        sb.AppendLine("WHERE FormID=@FormID; ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);

    }

    
    #endregion

    #region "ucReleaseForm.ascx"

    public DataTable ucReleaseForm_Select(string strFormID)
    {

        SqlParameter[] sqlParams = new SqlParameter[1];

        sqlParams[0] = new SqlParameter("@FormID", SqlDbType.VarChar, 50);
        sqlParams[0].Value = strFormID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT ReleaseFormStatus ");
        sb.AppendLine("FROM FD_caseReportForm ");
        sb.AppendLine("WHERE FormID = @FormID ; ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        if (DS != null && DS.Tables.Count > 0)
        {
            return DS.Tables[0];
        }
        else
        {
            return null;
        }


    }

    public DataTable ucReleaseForm_Select1(string strFormID)
    {

        SqlParameter[] sqlParams = new SqlParameter[1];

        sqlParams[0] = new SqlParameter("@FormID", SqlDbType.VarChar, 50);
        sqlParams[0].Value = strFormID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT FormID,AppropriationDate,AppropriationAcc,AppropriationMoney,TransferDocID,TransferDate ");
        sb.AppendLine("FROM FD_CaseReportForm ");
        sb.AppendLine("WHERE FormID = @FormID ; ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        if (DS != null && DS.Tables.Count > 0)
        {
            return DS.Tables[0];
        }
        else
        {
            return null;
        }


    }

    public int ucReleaseForm_Update(String original_FormID, String ReleaseFormStatus)
    {

        SqlParameter[] sqlParams = new SqlParameter[2];

        sqlParams[0] = new SqlParameter("@FormID", SqlDbType.NVarChar, 50);
        sqlParams[0].Value = original_FormID;
        sqlParams[1] = new SqlParameter("@ReleaseFormStatus", SqlDbType.NVarChar, 50);
        sqlParams[1].Value = ReleaseFormStatus;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("UPDATE FD_CaseReportForm ");
        sb.AppendLine("SET ReleaseFormStatus=@ReleaseFormStatus ");
        sb.AppendLine("WHERE FormID=@original_FormID; ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);

    }

    public int ucReleaseForm_Update(String original_FormID, DateTime AppropriationDate, string AppropriationAcc, int AppropriationMoney, string TransferDocID, DateTime TransferDate)
    {

        SqlParameter[] sqlParams = new SqlParameter[6];

        sqlParams[0] = new SqlParameter("@original_FormID", SqlDbType.NVarChar, 50);
        sqlParams[0].Value = original_FormID;
        sqlParams[1] = new SqlParameter("@AppropriationDate", SqlDbType.DateTime);
        sqlParams[1].Value = AppropriationDate;
        sqlParams[2] = new SqlParameter("@AppropriationAcc", SqlDbType.NVarChar, 50);
        sqlParams[2].Value = AppropriationAcc;
        sqlParams[3] = new SqlParameter("@AppropriationMoney", SqlDbType.Int);
        sqlParams[3].Value = AppropriationMoney;
        sqlParams[4] = new SqlParameter("@TransferDocID", SqlDbType.NVarChar, 50);
        sqlParams[4].Value = TransferDocID;
        sqlParams[5] = new SqlParameter("@TransferDate", SqlDbType.DateTime);
        sqlParams[5].Value = TransferDate;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("UPDATE FD_CaseReportForm ");
        sb.AppendLine("SET AppropriationDate=@AppropriationDate, ");
        sb.AppendLine("AppropriationAcc=@AppropriationAcc, ");
        sb.AppendLine("AppropriationMoney=@AppropriationMoney, ");
        sb.AppendLine("TransferDocID=@TransferDocID, ");
        sb.AppendLine("TransferDate=@TransferDate ");
        sb.AppendLine("WHERE FormID=@original_FormID; ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);

    }







    #endregion

    #region "ucActionForm_PetitionDoc"

    //SELECT
    public DataTable ucActionForm_PetitionDoc_Select(string FormID)
    {
        DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtActionForm_PetitionDoc;

        if (MyDataTable == null)
        {
            SqlParameter[] sqlParams = new SqlParameter[1];

            sqlParams[0] = new SqlParameter("@FormID", SqlDbType.NVarChar, 20);
            sqlParams[0].Value = FormID;

            StringBuilder sb = new StringBuilder();

            sb.AppendLine("SELECT * FROM FD_CaseReportForm_PetitionDoc WHERE FormID=@FormID ");

            DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

            DS.Tables[0].Columns[0].AutoIncrement = true;

            if (DS != null && DS.Tables.Count > 0)
            {
                MyDataTable = DS.Tables[0];
            }
            else
            {
                MyDataTable = null;
            }
        }

        ((clsPageBase)HttpContext.Current.Handler).dtActionForm_PetitionDoc = MyDataTable;

        return MyDataTable;

    }

    //INSERT
    public Int32 ucActionForm_PetitionDoc_Insert(String FormID, String PetitionDocID, String PetitionDocDate, string A_DocID, string B_DocID)
    {
        int iResult = 0;

        try
        {
            DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtActionForm_PetitionDoc;

            if (MyDataTable == null)
            {
                MyDataTable = ucActionForm_PetitionDoc_Select(string.Empty);
            }
            else
            {
                //檢查資料列是否已存在
                if (MyDataTable.Select(string.Format("PetitionDocID='{0}'", PetitionDocID)).Length > 0)
                {
                    throw new Exception(string.Format("訴願書文號[{0}]已存在!", PetitionDocID));
                }
            }

            DataRow drNewRow = MyDataTable.NewRow();

            drNewRow["FormID"] = FormID;
            drNewRow["PetitionDocID"] = PetitionDocID;

            if (PetitionDocDate.Trim() == string.Empty)
            {
                drNewRow["PetitionDocDate"] = System.DBNull.Value;
            }
            else
            {
                drNewRow["PetitionDocDate"] = Convert.ToDateTime(PetitionDocDate);
            }

            drNewRow["A_DocID"] = A_DocID;
            drNewRow["B_DocID"] = B_DocID;

            MyDataTable.Rows.Add(drNewRow);

            iResult = 1;
            return iResult;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

    }

    //UPDATE
    public Int32 ucActionForm_PetitionDoc_Update(Int32 original_id, String PetitionDocID, String PetitionDocDate, string A_DocID, string B_DocID)
    {
        int iResult = 0;

        try
        {
            DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtActionForm_PetitionDoc;

            if (MyDataTable == null)
            {
                throw new Exception("無法Update:dtActionForm_PetitionDoc不存在!");
            }

            DataRow[] drRows = MyDataTable.Select(string.Format("id={0}", original_id));

            drRows[0]["PetitionDocID"] = PetitionDocID;

            if (PetitionDocDate.Trim() == string.Empty)
            {
                drRows[0]["PetitionDocDate"] = System.DBNull.Value;
            }
            else
            {
                drRows[0]["PetitionDocDate"] = Convert.ToDateTime(PetitionDocDate);
            }

            drRows[0]["A_DocID"] = A_DocID;
            drRows[0]["B_DocID"] = B_DocID;

            iResult = 1;

            return iResult;

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

    }

    //DELETE
    public Int32 ucActionForm_PetitionDoc_Delete(Int32 original_id)
    {
        int iResult = 0;

        try
        {
            DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtActionForm_PetitionDoc;

            foreach (DataRow objDataRow in MyDataTable.Select(string.Format("id={0}", original_id)))
            {
                objDataRow.Delete();
                iResult++;
            }

            if (iResult != 1)
            {
                throw new Exception("Delete筆數不為1:" + iResult.ToString());
            }

            return iResult;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

    }

    #endregion

    #region "ucActionForm_PunishDoc"

    //SELECT
    public DataTable ucActionForm_PunishDoc_Select(string FormID)
    {
        DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtActionForm_PunishDoc;

        if (MyDataTable == null)
        {
            SqlParameter[] sqlParams = new SqlParameter[1];

            sqlParams[0] = new SqlParameter("@FormID", SqlDbType.NVarChar, 20);
            sqlParams[0].Value = FormID;

            StringBuilder sb = new StringBuilder();

            sb.AppendLine("SELECT * FROM FD_CaseReportForm_PunishDoc WHERE FormID=@FormID ");

            DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

            DS.Tables[0].Columns[0].AutoIncrement = true;

            if (DS != null && DS.Tables.Count > 0)
            {
                MyDataTable = DS.Tables[0];
            }
            else
            {
                MyDataTable = null;
            }
        }

        ((clsPageBase)HttpContext.Current.Handler).dtActionForm_PunishDoc = MyDataTable;

        return MyDataTable;

    }

    //INSERT
    public int ucActionForm_PunishDoc_Insert(String FormID, String PunishDocID, String PunishDocDate, String PunishDocMoney)
    {
        int iResult = 0;

        try
        {
            DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtActionForm_PunishDoc;

            if (MyDataTable == null)
            {
                MyDataTable = ucActionForm_PunishDoc_Select(string.Empty);
            }
            else
            {
                //檢查資料列是否已存在
                if (MyDataTable.Select(string.Format("PunishDocID='{0}'", PunishDocID)).Length > 0)
                {
                    throw new Exception(string.Format("處分書文號[{0}]已存在!", PunishDocID));
                }
            }

            DataRow drNewRow = MyDataTable.NewRow();

            drNewRow["FormID"] = FormID;
            drNewRow["PunishDocID"] = PunishDocID;

            if (PunishDocDate.Trim() == string.Empty)
            {
                drNewRow["PunishDocDate"] = System.DBNull.Value;
            }
            else
            {
                drNewRow["PunishDocDate"] = Convert.ToDateTime(PunishDocDate);
            }

            if (PunishDocMoney.Trim() == string.Empty)
            {
                drNewRow["PunishDocMoney"] = System.DBNull.Value;
            }
            else
            {
                drNewRow["PunishDocMoney"] = Convert.ToString(PunishDocMoney);
            }

            MyDataTable.Rows.Add(drNewRow);

            iResult = 1;
            return iResult;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

    }

    //UPDATE
    public int ucActionForm_PunishDoc_Update(int original_id, String PunishDocID, String PunishDocDate, String PunishDocMoney)
    {
        int iResult = 0;

        try
        {
            DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtActionForm_PunishDoc;

            if (MyDataTable == null)
            {
                throw new Exception("無法Update:dtCaseReportForm_DescDocNotice不存在!");
            }

            DataRow[] drRows = MyDataTable.Select(string.Format("id={0}", original_id));

            drRows[0]["PunishDocID"] = PunishDocID;

            if (PunishDocDate.Trim() == string.Empty)
            {
                drRows[0]["PunishDocDate"] = System.DBNull.Value;
            }
            else
            {
                drRows[0]["PunishDocDate"] = Convert.ToDateTime(PunishDocDate);
            }

            if (PunishDocMoney.Trim() == string.Empty)
            {
                drRows[0]["PunishDocMoney"] = System.DBNull.Value;
            }
            else
            {
                drRows[0]["PunishDocMoney"] = Convert.ToString(PunishDocMoney);
            }

            iResult = 1;

            return iResult;

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

    }

    //DELETE
    public int ucActionForm_PunishDoc_Delete(int original_id)
    {
        int iResult = 0;

        try
        {
            DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtActionForm_PunishDoc;

            foreach (DataRow objDataRow in MyDataTable.Select(string.Format("id={0}", original_id)))
            {
                objDataRow.Delete();
                iResult++;
            }

            if (iResult != 1)
            {
                throw new Exception("Delete筆數不為1:" + iResult.ToString());
            }

            return iResult;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

    }

    #endregion

    #region "ucActionForm_TribDoc"

    //SELECT
    public DataTable ucActionForm_TribDoc_Select(string FormID)
    {
        DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtActionForm_TribDoc;

        if (MyDataTable == null)
        {
            SqlParameter[] sqlParams = new SqlParameter[1];

            sqlParams[0] = new SqlParameter("@FormID", SqlDbType.NVarChar, 20);
            sqlParams[0].Value = FormID;

            StringBuilder sb = new StringBuilder();

            sb.AppendLine("SELECT * FROM FD_CaseReportForm_TribDoc  WHERE FormID=@FormID ");

            DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

            DS.Tables[0].Columns[0].AutoIncrement = true;

            if (DS != null && DS.Tables.Count > 0)
            {
                MyDataTable = DS.Tables[0];
            }
            else
            {
                MyDataTable = null;
            }
        }

        ((clsPageBase)HttpContext.Current.Handler).dtActionForm_TribDoc = MyDataTable;

        return MyDataTable;

    }

    //INSERT
    public int ucActionForm_TribDoc_Insert(String FormID, String TribDocID, String TribDocDate, String TribDocReson)
    {
        int iResult = 0;

        try
        {
            DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtActionForm_TribDoc;

            if (MyDataTable == null)
            {
                MyDataTable = ucActionForm_TribDoc_Select(string.Empty);
            }
            else
            {
                //檢查資料列是否已存在
                if (MyDataTable.Select(string.Format("TribDocID='{0}'", TribDocID)).Length > 0)
                {
                    throw new Exception(string.Format("裁處書案號[{0}]已存在!", TribDocID));
                }
            }

            DataRow drNewRow = MyDataTable.NewRow();

            drNewRow["FormID"] = FormID;
            drNewRow["TribDocID"] = TribDocID;

            if (TribDocDate.Trim() == string.Empty)
            {
                drNewRow["TribDocDate"] = System.DBNull.Value;
            }
            else
            {
                drNewRow["TribDocDate"] = Convert.ToDateTime(TribDocDate);
            }

            if (TribDocReson.Trim() == string.Empty)
            {
                drNewRow["TribDocReson"] = System.DBNull.Value;
            }
            else
            {
                drNewRow["TribDocReson"] = Convert.ToString(TribDocReson);
            }

            MyDataTable.Rows.Add(drNewRow);

            iResult = 1;
            return iResult;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

    }

    //UPDATE
    public int ucActionForm_TribDoc_Update(int original_id, String TribDocID, String TribDocDate, String TribDocReson)
    {
        int iResult = 0;

        try
        {
            DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtActionForm_TribDoc;

            if (MyDataTable == null)
            {
                throw new Exception("無法Update:dtCaseReportForm_DescDocNotice不存在!");
            }

            DataRow[] drRows = MyDataTable.Select(string.Format("id={0}", original_id));

            drRows[0]["TribDocID"] = TribDocID;

            if (TribDocDate.Trim() == string.Empty)
            {
                drRows[0]["TribDocDate"] = System.DBNull.Value;
            }
            else
            {
                drRows[0]["TribDocDate"] = Convert.ToDateTime(TribDocDate);
            }

            if (TribDocReson.Trim() == string.Empty)
            {
                drRows[0]["TribDocReson"] = System.DBNull.Value;
            }
            else
            {
                drRows[0]["TribDocReson"] = Convert.ToString(TribDocReson);
            }

            iResult = 1;

            return iResult;

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

    }

    //DELETE
    public int ucActionForm_TribDoc_Delete(int original_id)
    {
        int iResult = 0;

        try
        {
            DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtActionForm_TribDoc;

            foreach (DataRow objDataRow in MyDataTable.Select(string.Format("id={0}", original_id)))
            {
                objDataRow.Delete();
                iResult++;
            }

            if (iResult != 1)
            {
                throw new Exception("Delete筆數不為1:" + iResult.ToString());
            }

            return iResult;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

    }

    #endregion




    #region "ucReleaseForm_Pay"

    //SELECT
    public DataTable ucReleaseForm_Pay_Select(string FormID)
    {
        DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtReleaseForm_Pay;

        if (MyDataTable == null)
        {
            SqlParameter[] sqlParams = new SqlParameter[1];

            sqlParams[0] = new SqlParameter("@FormID", SqlDbType.NVarChar, 20);
            sqlParams[0].Value = FormID;

            StringBuilder sb = new StringBuilder();

            sb.AppendLine("SELECT * FROM FD_CaseReportForm_Pay WHERE FormID=@FormID ");

            DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

            DS.Tables[0].Columns[0].AutoIncrement = true;

            if (DS != null && DS.Tables.Count > 0)
            {
                MyDataTable = DS.Tables[0];
            }
            else
            {
                MyDataTable = null;
            }
        }

        ((clsPageBase)HttpContext.Current.Handler).dtReleaseForm_Pay = MyDataTable;

        return MyDataTable;

    }

    //INSERT
    public int ucReleaseForm_Pay_Insert(String FormID, DateTime? PayDate, String ReceiptID, DateTime? ReceiptDate)
    {
        int iResult = 0;

        try
        {
            DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtReleaseForm_Pay;

            if (MyDataTable == null)
            {
                MyDataTable = ucReleaseForm_Pay_Select(string.Empty);
            }


            DataRow drNewRow = MyDataTable.NewRow();

            drNewRow["FormID"] = FormID;

            if (PayDate ==null)
            {
                drNewRow["PayDate"] = System.DBNull.Value;
            }
            else
            {
                drNewRow["PayDate"] = PayDate;
            }

            drNewRow["ReceiptID"] = ReceiptID;

            if (ReceiptDate == null)
            {
                drNewRow["ReceiptDate"] = System.DBNull.Value;
            }
            else
            {
                drNewRow["ReceiptDate"] = ReceiptDate;
            }

            MyDataTable.Rows.Add(drNewRow);

            iResult = 1;
            return iResult;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

    }

    //UPDATE
    public int ucReleaseForm_Pay_Update(Int32 original_id, DateTime? PayDate, String ReceiptID, DateTime? ReceiptDate)
    {
        int iResult = 0;

        try
        {
            DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtReleaseForm_Pay;

            if (MyDataTable == null)
            {
                throw new Exception("無法Update:dtReleaseForm_Pay不存在!");
            }

            DataRow[] drRows = MyDataTable.Select(string.Format("id={0}", original_id));

            if (PayDate==null)
            {
                drRows[0]["PayDate"] = System.DBNull.Value;
            }
            else
            {
                drRows[0]["PayDate"] = PayDate;
            }

            drRows[0]["ReceiptID"] = ReceiptID;

            if (ReceiptDate == null)
            {
                drRows[0]["ReceiptDate"] = System.DBNull.Value;
            }
            else
            {
                drRows[0]["ReceiptDate"] = ReceiptDate;
            }

            iResult = 1;

            return iResult;

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

    }

    //DELETE
    public int ucReleaseForm_Pay_Delete(Int32 original_id)
    {
        int iResult = 0;

        try
        {
            DataTable MyDataTable = ((clsPageBase)HttpContext.Current.Handler).dtReleaseForm_Pay;

            foreach (DataRow objDataRow in MyDataTable.Select(string.Format("id={0}", original_id)))
            {
                objDataRow.Delete();
                iResult++;
            }

            if (iResult != 1)
            {
                throw new Exception("Delete筆數不為1:" + iResult.ToString());
            }

            return iResult;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

    }

    #endregion





    #region "檢舉單查詢相關"
    public DataTable CaseReportFormQuery_SELECT(string FormID, string StartDate, string EndDate, int OrganizationID, string Place, string Car_ID,string Car_OwnerID,string Car_OwnerName ,string Action, string ActionOther)
    {
        SqlParameter[] sqlParams = new SqlParameter[10];

        sqlParams[0] = new SqlParameter("@FormID", SqlDbType.NVarChar, 20);
        sqlParams[0].Value = FormID;
        sqlParams[1] = new SqlParameter("@StartDate", SqlDbType.NVarChar, 10);
        sqlParams[1].Value = StartDate;
        sqlParams[2] = new SqlParameter("@EndDate", SqlDbType.NVarChar, 10);
        sqlParams[2].Value = EndDate;
        sqlParams[3] = new SqlParameter("@OrganizationID", SqlDbType.Int);
        sqlParams[3].Value = OrganizationID;
        sqlParams[4] = new SqlParameter("@Place", SqlDbType.NVarChar, 50);
        sqlParams[4].Value = Place;
        sqlParams[5] = new SqlParameter("@Car_ID", SqlDbType.NVarChar, 50);
        sqlParams[5].Value = Car_ID;
        sqlParams[6] = new SqlParameter("@Car_OwnerID", SqlDbType.NVarChar, 50);
        sqlParams[6].Value = Car_OwnerID;
        sqlParams[7] = new SqlParameter("@Car_OwnerName", SqlDbType.NVarChar, 50);
        sqlParams[7].Value = Car_OwnerName;
        sqlParams[8] = new SqlParameter("@Action", SqlDbType.NVarChar,10);
        sqlParams[8].Value = Action;
        sqlParams[9] = new SqlParameter("@ActionOther", SqlDbType.NVarChar, 50);
        sqlParams[9].Value = ActionOther;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT A.FormID,A.[Date] as theDate,B.RegionName+ A.Place as OrganizationName,C.Text as FormStatus ");
        sb.AppendLine("FROM FD_CaseReportForm A ");
        sb.AppendLine("left join FD_view_RegionOrganization B on A.OrganizationID=B.OrganizationID ");
        sb.AppendLine("left join FD_CodeMapping C on A.FormStatus=C.Value and C.TableName='CaseReportForm' and C.ColumnName='FormStatus' and C.Active='Y' ");
        sb.AppendLine("left join (SELECT FormID,MAX(id) as id FROM FD_CaseReportForm_CarOwner GROUP BY FormID) D on A.FormID=D.FormID ");
        sb.AppendLine("left join FD_CaseReportForm_CarOwner E on D.FormID=E.FormID AND D.id=E.id");
        
        sb.AppendLine("WHERE C.Value>=1 ");

        if (!string.IsNullOrEmpty(FormID))
        { 
            sb.AppendLine("AND A.FormID like '%' + @FormID + '%' ");      
        }

        if (!string.IsNullOrEmpty(StartDate))
        {
            sb.AppendLine("AND A.[Date]>=@StartDate ");
        }

        if (!string.IsNullOrEmpty(EndDate))
        {
            sb.AppendLine("AND A.[Date]<=@EndDate ");
        }

        if (OrganizationID!=0)
        {
            sb.AppendLine("AND A.OrganizationID=@OrganizationID ");
        }

        if (!string.IsNullOrEmpty(Place))
        {
            sb.AppendLine("AND A.Place like '%' + @Place + '%' ");      
        }

        if (!string.IsNullOrEmpty(Car_ID))
        {
            sb.AppendLine("AND A.Car_ID like '%' + @Car_ID + '%' ");
        }

        if (!string.IsNullOrEmpty(Car_OwnerID))
        {
            sb.AppendLine("AND E.Car_OwnerID like '%' + @Car_OwnerID + '%' ");
        }

        if (!string.IsNullOrEmpty(Car_OwnerName))
        {
            sb.AppendLine("AND E.Car_OwnerName like '%' + @Car_OwnerName + '%' ");
        }
        
        if (!string.IsNullOrEmpty(Action))
        {
            sb.AppendLine("AND (1=2 ");

            foreach (string strTMP in Action.Split(','))
            {
                sb.AppendFormat("OR CHARINDEX(',{0}',A.[Action],1)>=1 ", strTMP);            
            }

            sb.AppendLine(") ");
        }

        if (!string.IsNullOrEmpty(ActionOther))
        {
            sb.AppendLine("AND A.ActionOther like '%' + @ActionOther + '%' ");
        }

        sb.AppendLine("ORDER BY A.FormID DESC ");
        
        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        return DS.Tables[0];

    }

    public DataTable CaseReportFormQuery_SELECTByUser(string FormID, string StartDate, string EndDate, int OrganizationID, string Place, string Car_ID,int FormStatus, string Action, string ActionOther, string ReporterID)
    {
        SqlParameter[] sqlParams = new SqlParameter[10];

        sqlParams[0] = new SqlParameter("@FormID", SqlDbType.NVarChar, 20);
        sqlParams[0].Value = FormID;
        sqlParams[1] = new SqlParameter("@StartDate", SqlDbType.NVarChar, 10);
        sqlParams[1].Value = StartDate;
        sqlParams[2] = new SqlParameter("@EndDate", SqlDbType.NVarChar, 10);
        sqlParams[2].Value = EndDate;
        sqlParams[3] = new SqlParameter("@OrganizationID", SqlDbType.Int);
        sqlParams[3].Value = OrganizationID;
        sqlParams[4] = new SqlParameter("@Place", SqlDbType.NVarChar, 50);
        sqlParams[4].Value = Place;
        sqlParams[5] = new SqlParameter("@Car_ID", SqlDbType.NVarChar, 50);
        sqlParams[5].Value = Car_ID;
        sqlParams[6] = new SqlParameter("@FormStatus", SqlDbType.Int);
        sqlParams[6].Value = FormStatus;
        sqlParams[7] = new SqlParameter("@Action", SqlDbType.NVarChar, 10);
        sqlParams[7].Value = Action;
        sqlParams[8] = new SqlParameter("@ActionOther", SqlDbType.NVarChar, 50);
        sqlParams[8].Value = ActionOther;
        sqlParams[9] = new SqlParameter("@ReporterID", SqlDbType.NVarChar, 20);
        sqlParams[9].Value = ReporterID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT sn=identity(int,1,1),A.FormID,A.ReportDate,A.Car_ID,B.RegionName+ A.Place as OrganizationName,C.Text as FormStatus ");
        sb.AppendLine("into #tmp ");
        sb.AppendLine("FROM FD_CaseReportForm A ");
        sb.AppendLine("left join FD_view_RegionOrganization B on A.OrganizationID=B.OrganizationID ");
        sb.AppendLine("left join FD_CodeMapping C on A.FormStatus=C.Value and C.TableName='CaseReportForm' and C.ColumnName='FormStatus' and C.Active='Y' ");
        sb.AppendLine("WHERE ((A.DisplayReporterID=@ReporterID and A.FormStatus>0) or (A.ReporterID=@ReporterID)) ");

        if (!string.IsNullOrEmpty(FormID))
        {
            sb.AppendLine("AND A.FormID like '%' + @FormID + '%' ");
        }

        if (!string.IsNullOrEmpty(StartDate))
        {
            sb.AppendLine("AND A.[Date]>=@StartDate ");
        }

        if (!string.IsNullOrEmpty(EndDate))
        {
            sb.AppendLine("AND A.[Date]<=@EndDate ");
        }

        if (OrganizationID != 0)
        {
            sb.AppendLine("AND A.OrganizationID=@OrganizationID ");
        }

        if (!string.IsNullOrEmpty(Place))
        {
            sb.AppendLine("AND A.Place like '%' + @Place + '%' ");
        }

        if (!string.IsNullOrEmpty(Car_ID))
        {
            sb.AppendLine("AND A.Car_ID like '%' + @Car_ID + '%' ");
        }

        if (FormStatus != 0)
        {
            sb.AppendLine("AND A.FormStatus=@FormStatus ");
        }

        if (!string.IsNullOrEmpty(Action))
        {
            sb.AppendLine("AND (1=2 ");

            foreach (string strTMP in Action.Split(','))
            {
                sb.AppendFormat("OR CHARINDEX(',{0}',A.[Action],1)>=1 ", strTMP);
            }

            sb.AppendLine(") ");
        }

        if (!string.IsNullOrEmpty(ActionOther))
        {
            sb.AppendLine("AND A.ActionOther like '%' + @ActionOther + '%' ");
        }

        sb.AppendLine("ORDER BY A.FormID DESC;SELECT * FROM #tmp; ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        return DS.Tables[0];

    }

    
    #endregion


    #region "ManageOrganization"

    //OrganizationTree_Select
    //public DataTable OrganizationTree_Select(string Active)
    //{
    //    SqlParameter[] sqlParams = new SqlParameter[1];

    //    sqlParams[0] = new SqlParameter("@Active", SqlDbType.NChar,1);
    //    sqlParams[0].Value = Active;

    //    StringBuilder sb = new StringBuilder();

    //    sb.AppendLine("SELECT A.OrganizationID,A.OrganizationName,ISNULL(A.Parent,0) as Parent,A.Internal,A.Active,A.OrganizationCode,A.InternalCode ");
    //    sb.AppendLine("FROM Organization A ");

    //    if (Active == "Y" || Active == "N")
    //    {
    //        sb.AppendLine("WHERE Active = @Active ");
    //    }

    //    DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

    //    return DS.Tables[0];

    //}

    //SELECT
    public DataTable OrganizationTree_Select(int OrganizationPapaID)
    {
        SqlParameter[] sqlParams = new SqlParameter[1];

        sqlParams[0] = new SqlParameter("@OrganizationPapaID", SqlDbType.Int);
        sqlParams[0].Value = OrganizationPapaID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT A.OrganizationID,A.OrganizationName,ISNULL(A.Parent,0) as Parent,A.Internal,A.Active,A.OrganizationCode,A.InternalCode ");
        sb.AppendLine("FROM Organization A ");
        sb.AppendFormat("inner join (SELECT * FROM dbo.fn_GetAllSonsByOrganizationID(@OrganizationPapaID)) Papa on A.OrganizationID=Papa.[Value] ");
        sb.AppendLine("ORDER BY Papa.SN; ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        return DS.Tables[0];

    }

    //SELECT
    public DataTable ManageOrganization_Select(int OrganizationID)
    {
        SqlParameter[] sqlParams = new SqlParameter[1];

        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.Int);
        sqlParams[0].Value = OrganizationID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT B.OrganizationName as ParentName,A.OrganizationID,A.OrganizationName,ISNULL(A.Parent,0) as Parent,A.Internal,A.Active ");
        sb.AppendLine("FROM Organization A ");
        sb.AppendLine("left join Organization B on A.Parent=B.OrganizationID ");
        sb.AppendLine("WHERE A.OrganizationID = @OrganizationID ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        return DS.Tables[0];

    }


    //INSERT
    public int ManageOrganization_Insert(string OrganizationName, int Parent, string Internal, string Active)
    {

        SqlParameter[] sqlParams = new SqlParameter[4];

        sqlParams[0] = new SqlParameter("@OrganizationName", SqlDbType.NVarChar, 50);
        sqlParams[0].Value = OrganizationName;
        sqlParams[1] = new SqlParameter("@Parent", SqlDbType.Int);

        if (Parent == 0)
        {
            sqlParams[1].Value = DBNull.Value;
        }
        else
        {
            sqlParams[1].Value = Parent;
        }

        sqlParams[2] = new SqlParameter("@Internal", SqlDbType.NChar, 1);
        sqlParams[2].Value = Internal;
        sqlParams[3] = new SqlParameter("@Active", SqlDbType.NChar, 1);
        sqlParams[3].Value = Active;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("INSERT Organization ([OrganizationName],[Parent],[Internal],[Active]) ");
        sb.AppendLine("SELECT  ");
        sb.AppendLine("@OrganizationName,@Parent,@Internal,@Active ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);

    }

    //UPDATE
    public int ManageOrganization_Update(int original_OrganizationID, string OrganizationName, string Internal, string Active)
    {
        SqlParameter[] sqlParams = new SqlParameter[4];

        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.Int);
        sqlParams[0].Value = original_OrganizationID;
        sqlParams[1] = new SqlParameter("@OrganizationName", SqlDbType.NVarChar, 50);
        sqlParams[1].Value = OrganizationName;
        sqlParams[2] = new SqlParameter("@Internal", SqlDbType.NChar, 1);
        sqlParams[2].Value = Internal;
        sqlParams[3] = new SqlParameter("@Active", SqlDbType.NChar, 1);
        sqlParams[3].Value = Active;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("UPDATE Organization ");
        sb.AppendLine("SET [OrganizationName]=@OrganizationName ");
        sb.AppendLine(",[Internal]=@Internal ");
        sb.AppendLine(",[Active]=@Active ");
        sb.AppendLine("WHERE OrganizationID=@OrganizationID ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);
     
    }

    //DisableEnable
    public int ManageOrganization_DisableEnable(int original_OrganizationID, string Active)
    {
        SqlParameter[] sqlParams = new SqlParameter[2];

        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.Int);
        sqlParams[0].Value = original_OrganizationID;
        sqlParams[1] = new SqlParameter("@Active", SqlDbType.NChar, 1);
        sqlParams[1].Value = Active;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("UPDATE Organization ");
        sb.AppendLine("SET [Active]=@Active ");
        sb.AppendLine("WHERE OrganizationID=@OrganizationID ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);
    }




    #endregion

    #region "ManageRole"

    //SELECT
    public DataTable SelectAllRoles()
    {

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT A.RoleID,(Papa.BarText+A.RoleName) as RoleName ");
        sb.AppendLine("FROM FD_Role A ");
        sb.AppendLine("inner join (SELECT * FROM dbo.fn_GetAllSonsByRoleID('',0)) Papa on A.RoleID=Papa.[Value] ");
        sb.AppendLine("ORDER BY Papa.SN; ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), null);

        return DS.Tables[0];

    }

    //SELECT
    public DataTable ManageRole_Select(string PapaID)
    {
        SqlParameter[] sqlParams = new SqlParameter[1];

        sqlParams[0] = new SqlParameter("@PapaID", SqlDbType.Int);
        sqlParams[0].Value = PapaID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT A.RoleID,A.RoleName,A.Parent,A.RoleDescription,A.Active,A.CanEdit,B.RoleName as ParentName ");
        sb.AppendLine("FROM FD_Role A ");
        sb.AppendLine("inner join (SELECT * FROM dbo.fn_GetAllSonsByRoleID('',@PapaID)) Papa on A.RoleID=Papa.[Value] ");
        sb.AppendLine("inner join FD_Role B on A.Parent=B.RoleID ");
        sb.AppendLine("WHERE A.Active='Y' ");
        sb.AppendLine("ORDER BY Papa.SN; ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        return DS.Tables[0];

    }

    //INSERT
    public Int32 ManageRole_Insert(int Parent, string RoleName, string RoleDescription)
    {

        SqlParameter[] sqlParams = new SqlParameter[3];

        sqlParams[0] = new SqlParameter("@Parent", SqlDbType.Int);
        sqlParams[0].Value = Parent;
        sqlParams[1] = new SqlParameter("@RoleName", SqlDbType.NVarChar,50);
        sqlParams[1].Value = RoleName;
        sqlParams[2] = new SqlParameter("@RoleDescription", SqlDbType.NVarChar, 50);
        sqlParams[2].Value = RoleDescription;


        StringBuilder sb = new StringBuilder();

        sb.AppendLine("INSERT FD_Role ([RoleName],[RoleDescription],[Parent],[Active],[CanEdit]) ");
        sb.AppendLine("SELECT  ");
        sb.AppendLine("@RoleName,@RoleDescription,@Parent,'Y','Y' ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);

    }

    //UPDATE
    public Int32 ManageRole_Update(int original_RoleID, string RoleName, string RoleDescription, string Active)
    {
        SqlParameter[] sqlParams = new SqlParameter[4];

        sqlParams[0] = new SqlParameter("@RoleID", SqlDbType.Int);
        sqlParams[0].Value = original_RoleID;
        sqlParams[1] = new SqlParameter("@RoleName", SqlDbType.NVarChar,50);
        sqlParams[1].Value = RoleName;
        sqlParams[2] = new SqlParameter("@RoleDescription", SqlDbType.NVarChar, 50);
        sqlParams[2].Value = RoleDescription;
        sqlParams[3] = new SqlParameter("@Active", SqlDbType.NChar, 1);
        sqlParams[3].Value = Active;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("UPDATE FD_Role ");
        sb.AppendLine("SET [RoleName]=@RoleName ");
        sb.AppendLine(",[RoleDescription]=@RoleDescription ");
        sb.AppendLine(",[Active]=@Active ");
        sb.AppendLine("WHERE RoleID=@RoleID ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);
    }

    //Delete
    public Int32 ManageRole_Delete(int original_RoleID)
    {
        SqlParameter[] sqlParams = new SqlParameter[1];

        sqlParams[0] = new SqlParameter("@RoleID", SqlDbType.Int);
        sqlParams[0].Value = original_RoleID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("DELETE FD_Role ");
        sb.AppendLine("WHERE RoleID=@RoleID ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);
    }

    #endregion

    #region "ManageRoleProgramMapping"

    //SELECT
    public DataTable ManageRoleProgramMapping_Select(int RoleID, int Parent)
    {
        SqlParameter[] sqlParams = new SqlParameter[2];

        sqlParams[0] = new SqlParameter("@RoleID", SqlDbType.Int);
        sqlParams[0].Value = RoleID;
        sqlParams[1] = new SqlParameter("@Parent", SqlDbType.Int);
        sqlParams[1].Value = Parent;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT A.ProgramID,A.ProgramName,A.Parent ");
        sb.AppendLine(",CASE WHEN B.ProgramID is null THEN (CASE WHEN C.ProgramID is null THEN '0' ELSE '1' END) ELSE '1' END as CanAccess ");
        sb.AppendLine(",CASE WHEN C.ProgramID is null THEN '1' ELSE '0' END as IsInherits ");
        sb.AppendLine("FROM FD_Program A ");
        sb.AppendLine("inner join (SELECT * FROM dbo.fn_GetAllSonsByProgramID(@Parent)) Papa on A.ProgramID=Papa.[Value] ");
        sb.AppendLine("left join FD_RoleProgramMapping B on A.ProgramID=B.ProgramID AND B.RoleID=@RoleID ");
        sb.AppendLine("left join FD_RoleProgramMapping C on A.ProgramID=C.ProgramID AND C.RoleID=(SELECT Parent FROM FD_Role WHERE RoleID=@RoleID) ");
        sb.AppendLine("WHERE A.Active='Y' AND A.Editable='Y' ");
        sb.AppendLine("ORDER BY Papa.SN; ");
         //sb.AppendLine(" ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        return DS.Tables[0];

    }

    //INSERT
    public Int32 ManageRoleProgramMapping_Insert(int RoleID, int ProgramID)
    {

        SqlParameter[] sqlParams = new SqlParameter[2];

        sqlParams[0] = new SqlParameter("@RoleID", SqlDbType.Int);
        sqlParams[0].Value = RoleID;
        sqlParams[1] = new SqlParameter("@ProgramID", SqlDbType.Int);
        sqlParams[1].Value = ProgramID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("Insert FD_RoleProgramMapping(RoleID,ProgramID,AuthName) ");
        sb.AppendLine("SELECT  ");
        sb.AppendLine("@RoleID,@ProgramID,@RoleID ");

        int intResult = 0;
        try
        {
            intResult = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);
        }
        catch (Exception ex)
        {

            if (((System.Data.SqlClient.SqlException)ex).Number == 2627)
            {
                CommonLib2.clsCommon.ShowMessage(true, CommonLib2.clsCommon.RegisterType.RegisterStartupScript, "功能已有存取權限!");
            }
            else
            {
                CommonLib2.clsCommon.ShowMessage(true, CommonLib2.clsCommon.RegisterType.RegisterStartupScript, ex.Message.Replace("'", "\\'"));
            }

        }

        return intResult;

    }

    //DELETE
    public Int32 ManageRoleProgramMapping_Delete(int original_RoleID, int original_ProgramID)
    {
        SqlParameter[] sqlParams = new SqlParameter[2];

        sqlParams[0] = new SqlParameter("@RoleID", SqlDbType.Int);
        sqlParams[0].Value = original_RoleID;
        sqlParams[1] = new SqlParameter("@ProgramID", SqlDbType.Int);
        sqlParams[1].Value = original_ProgramID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("DELETE FD_RoleProgramMapping ");
        sb.AppendLine("WHERE RoleID=@RoleID AND ProgramID=@ProgramID ");

        int intResult = 0;

        try
        {
            intResult = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);
        }
        catch (Exception ex)
        {
            CommonLib2.clsCommon.ShowMessage(true, CommonLib2.clsCommon.RegisterType.RegisterStartupScript, ex.Message.Replace("'", "\\'"));
        }

        return intResult;
    }



    #endregion

    #region "ManageUser"

    //SELECT
    public DataTable SelectMyOrganization(int MyOrganizationID)
    {

        StringBuilder sb = new StringBuilder();

        if (MyOrganizationID == 15)
        {
            sb.AppendLine("SELECT A.OrganizationID,A.OrganizationName ");
            sb.AppendLine("FROM Organization A ");
            sb.AppendFormat("WHERE A.Active='Y' ");
            sb.AppendLine("ORDER BY A.OrganizationID; ");
        }
        else
        {
            sb.AppendLine("SELECT A.OrganizationID,A.OrganizationName ");
            sb.AppendLine("FROM Organization A ");
            sb.AppendFormat("inner join (SELECT * FROM dbo.fn_GetAllSonsByOrganizationID({0}) UNION SELECT 0,'{0}') Papa on A.OrganizationID=Papa.[Value] ", MyOrganizationID);
            sb.AppendFormat("WHERE A.Active='Y' ");
            sb.AppendLine("ORDER BY Papa.SN; ");
        }

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), null);

        return DS.Tables[0];

    }

    public DataTable SelectMyRegion(int MyOrganizationID)
    {

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT A.RegionName,A.OrganizationID ");
        sb.AppendLine("FROM FD_view_RegionOrganization A ");
        sb.AppendFormat("inner join (SELECT * FROM dbo.fn_GetAllSonsByOrganizationID({0}) UNION SELECT 0,'{0}') Papa on A.OrganizationID=Papa.[Value] ", MyOrganizationID);
        sb.AppendLine("ORDER BY Papa.SN; ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), null);

        return DS.Tables[0];

    }



    //SELECT
    public DataTable ManageUser_Select(string EmployeeID, string EmployeeName, string OrganizationID, int MyOrganizationID)
    {
        SqlParameter[] sqlParams = new SqlParameter[3];

        sqlParams[0] = new SqlParameter("@EmployeeID", SqlDbType.NVarChar,50);
        sqlParams[0].Value = EmployeeID;
        sqlParams[1] = new SqlParameter("@EmployeeName", SqlDbType.NVarChar, 50);
        sqlParams[1].Value = EmployeeName;
        sqlParams[2] = new SqlParameter("@OrganizationID", SqlDbType.NVarChar, 50);
        sqlParams[2].Value = OrganizationID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT A.EmployeeID,A.EmployeeName,A.Password,B.OrganizationID,B.OrganizationName,A.Active ");
        sb.AppendLine("FROM FD_Employee A ");
        if (MyOrganizationID != 15)
        {
            sb.AppendFormat("inner join (SELECT * FROM dbo.fn_GetAllSonsByOrganizationID({0}) UNION SELECT 0,'{0}') Papa on A.OrganizationID=Papa.[Value] ", MyOrganizationID);
        }
        sb.AppendLine("left join dbo.Organization B on A.OrganizationID=B.OrganizationID ");
        sb.AppendLine("WHERE 1=1 ");
        sb.AppendLine("AND A.EmployeeID like '%'+ISNULL(@EmployeeID,'')+'%' ");
        sb.AppendLine("AND A.EmployeeName like '%'+ISNULL(@EmployeeName,'')+'%' ");
        sb.AppendLine("AND (A.OrganizationID = @OrganizationID or @OrganizationID='0')");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        return DS.Tables[0];

    }

    //INSERT
    public Int32 ManageUser_Insert(string EmployeeID, string EmployeeName, string Password, string OrganizationID,string Active)
    {

        SqlParameter[] sqlParams = new SqlParameter[5];

        sqlParams[0] = new SqlParameter("@EmployeeID", SqlDbType.NVarChar, 50);
        sqlParams[0].Value = EmployeeID;
        sqlParams[1] = new SqlParameter("@EmployeeName", SqlDbType.NVarChar, 50);
        sqlParams[1].Value = EmployeeName;
        sqlParams[2] = new SqlParameter("@Password", SqlDbType.NVarChar, 50);
        sqlParams[2].Value = Password;
        sqlParams[3] = new SqlParameter("@OrganizationID", SqlDbType.NVarChar, 50);
        sqlParams[3].Value = OrganizationID;
        sqlParams[4] = new SqlParameter("@Active", SqlDbType.NVarChar, 5);
        sqlParams[4].Value = Active;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("INSERT FD_Employee ([EmployeeID],[EmployeeName],[Password],[OrganizationID],[Active]) ");
        sb.AppendLine("SELECT  ");
        sb.AppendLine("@EmployeeID,@EmployeeName,@Password,@OrganizationID,@Active ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);

    }

    //UPDATE
    public Int32 ManageUser_Update(string original_EmployeeID, string EmployeeName, string Password, string OrganizationID, string Active)
    {
        SqlParameter[] sqlParams = new SqlParameter[5];

        sqlParams[0] = new SqlParameter("@EmployeeID", SqlDbType.NVarChar, 50);
        sqlParams[0].Value = original_EmployeeID;
        sqlParams[1] = new SqlParameter("@EmployeeName", SqlDbType.NVarChar, 50);
        sqlParams[1].Value = EmployeeName;
        sqlParams[2] = new SqlParameter("@Password", SqlDbType.NVarChar, 50);
        sqlParams[2].Value = Password;
        sqlParams[3] = new SqlParameter("@OrganizationID", SqlDbType.NVarChar, 50);
        sqlParams[3].Value = OrganizationID;
        sqlParams[4] = new SqlParameter("@Active", SqlDbType.NVarChar, 1);
        sqlParams[4].Value = Active;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("UPDATE FD_Employee ");
        sb.AppendLine("SET [EmployeeName]=@EmployeeName ");
        sb.AppendLine(",[Password]=@Password ");
        sb.AppendLine(",[OrganizationID]=@OrganizationID ");
        sb.AppendLine(",[Active]=@Active ");
        sb.AppendLine("WHERE EmployeeID=@EmployeeID ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);
    }

    //DELETE
    public Int32 ManageUser_Delete(string original_EmployeeID)
    {
        SqlParameter[] sqlParams = new SqlParameter[1];

        sqlParams[0] = new SqlParameter("@original_EmployeeID", SqlDbType.NVarChar, 50);
        sqlParams[0].Value = original_EmployeeID;
    
        StringBuilder sb = new StringBuilder();

        sb.AppendLine("DELETE FD_Employee WHERE EmployeeID=@original_EmployeeID ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);
    }


    #endregion

    #region "ManageUserRoleMapping"    

   //SELECT
    public DataTable ManageUserRoleMapping_SelectPapasWhoICanSet(string UserID)
    {
        SqlParameter[] sqlParams = new SqlParameter[1];

        sqlParams[0] = new SqlParameter("@UserID", SqlDbType.NVarChar, 50);
        sqlParams[0].Value = UserID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT A.RoleID,A.RoleName ");
        sb.AppendLine("FROM FD_Role A ");
        sb.AppendLine("inner join (SELECT * FROM dbo.fn_GetAllSonsByRoleID(@PapaID)) Papa on A.RoleID=Papa.[Value] ");
        //sb.AppendLine("left join FD_UserRoleMapping B on A.RoleID=B.RoleID AND B.UserID=@EmployeeID ");
        sb.AppendLine("left join FD_RoleUserMappingView B on A.RoleID=B.RoleID AND B.UserID=@EmployeeID ");
        sb.AppendLine("WHERE A.Active='Y' ");
        sb.AppendLine("ORDER BY Papa.SN; ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        return DS.Tables[0];

    }


    //SELECT
    public DataTable ManageUserRoleMapping_Select(string UserID,string EmployeeID)
    {
        SqlParameter[] sqlParams = new SqlParameter[2];

        sqlParams[0] = new SqlParameter("@UserID", SqlDbType.NVarChar, 50);
        sqlParams[0].Value = UserID;
        sqlParams[1] = new SqlParameter("@EmployeeID", SqlDbType.NVarChar, 50);
        sqlParams[1].Value = EmployeeID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT A.RoleID,A.RoleName,A.Parent,CASE WHEN B.RoleID is null THEN '0' ELSE '1' END as belong ");
        sb.AppendLine("FROM FD_Role A ");
        //sb.AppendLine("left join FD_UserRoleMapping B on A.RoleID=B.RoleID AND B.UserID=@EmployeeID ");
        sb.AppendLine("left join FD_RoleUserMappingView B on A.RoleID=B.RoleID AND B.UserID=@EmployeeID ");
        sb.AppendLine("WHERE A.Active='Y' ");
        sb.AppendLine("and  A.RoleID in (SELECT distinct * FROM dbo.fn_GetAllPapasByUserID(@UserID)) or 7 in (SELECT distinct * FROM dbo.fn_GetAllPapasByUserID(@UserID)) ");
        //sb.AppendLine("ORDER BY Papa.SN; ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        return DS.Tables[0];

    }

    //INSERT
    public Int32 ManageUserRoleMapping_Insert(int RoleID, string EmployeeID)
    {

        SqlParameter[] sqlParams = new SqlParameter[2];

        sqlParams[0] = new SqlParameter("@RoleID", SqlDbType.Int);
        sqlParams[0].Value = RoleID;
        sqlParams[1] = new SqlParameter("@EmployeeID", SqlDbType.NVarChar, 50);
        sqlParams[1].Value = EmployeeID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("Insert FD_UserRoleMapping(RoleID,UserID) ");
        sb.AppendLine("SELECT ");
        sb.AppendLine("@RoleID,@EmployeeID ");
        sb.AppendLine(";Insert FD_RoleUserMapping(RoleID,UserID) ");
        sb.AppendLine("SELECT ");
        sb.AppendLine("@RoleID,@EmployeeID ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);

    }

    //DELETE
    public Int32 ManageUserRoleMapping_Delete(int original_RoleID, string original_EmployeeID)
    {
        SqlParameter[] sqlParams = new SqlParameter[2];

        sqlParams[0] = new SqlParameter("@RoleID", SqlDbType.Int);
        sqlParams[0].Value = original_RoleID;
        sqlParams[1] = new SqlParameter("@EmployeeID", SqlDbType.NVarChar, 50);
        sqlParams[1].Value = original_EmployeeID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("DELETE FD_UserRoleMapping ");
        sb.AppendLine("WHERE RoleID=@RoleID AND UserID=@EmployeeID ");
        sb.AppendLine(";DELETE FD_RoleUserMapping ");
        sb.AppendLine("WHERE RoleID=@RoleID AND UserID=@EmployeeID ");
        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);
    }



    #endregion

    #region "BLL_Public"


    #endregion

    //public DataTable BLL_Public_SelectAllPapas(int RoleID)
    //{
    //    //如果RoleID不為0才要過濾
    //    SqlParameter[] sqlParams = new SqlParameter[1];

    //    sqlParams[0] = new SqlParameter("@RoleID", SqlDbType.Int);
    //    sqlParams[0].Value = RoleID;

    //    StringBuilder sb = new StringBuilder();

    //    sb.AppendLine("SELECT distinct A.RoleID,A.RoleName ");
    //    sb.AppendLine("FROM FD_Role A ");

    //    if (RoleID != 0)
    //    { 
    //        sb.AppendLine("inner join (SELECT * FROM dbo.fn_GetOnePapasByRoleID(@RoleID)) Papa on A.RoleID=Papa.[Value] ");        
    //    }

    //    sb.AppendLine("left join FD_Role B on A.RoleID=B.Parent ");
    //    sb.AppendLine("WHERE B.RoleID is not null ");
    //    sb.AppendLine("AND A.Active='Y' AND B.Active='Y' ");
    //    sb.AppendLine("ORDER BY A.RoleID ");

    //    DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), null);

    //    if (DS != null && DS.Tables.Count > 0 && DS.Tables[0].Rows.Count > 0)
    //    {
    //        return DS.Tables[0];
    //    }
    //    else
    //    {
    //        return null;
    //    }


    //}

    public void DataSetToDatabase(DataTable MyDataTable,string strSqlCommond)
    {
        if (MyDataTable != null)
        { 
            SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(strSqlCommond, conn);
            SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(mySqlDataAdapter);
            DataSet DS = new DataSet();
            DS.Tables.Add(MyDataTable);
            mySqlDataAdapter.Update(DS);
            MyDataTable.AcceptChanges();         
           
        }

    }

    public DataTable Get_CodeMapping(string TableName, string ColumnName)
    {

        SqlParameter[] sqlParams = new SqlParameter[2];

        sqlParams[0] = new SqlParameter("@TableName", SqlDbType.NVarChar, 50);
        sqlParams[0].Value = TableName;
        sqlParams[1] = new SqlParameter("@ColumnName", SqlDbType.NVarChar, 50);
        sqlParams[1].Value = ColumnName;



        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT Value,[Text] ");
        sb.AppendLine("FROM FD_CodeMapping ");
        sb.AppendLine("WHERE TableName=@TableName and ColumnName=@ColumnName and Active='Y' ");
        sb.AppendLine("ORDER BY Sequence ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        if (DS != null && DS.Tables.Count > 0)
        {
            DataRow dr = DS.Tables[0].NewRow();
            dr[0] = 0;
            dr[1] = "請選擇";

            DS.Tables[0].Rows.InsertAt(dr, 0);
            return DS.Tables[0];
        }
        else
        {
            return null;
        }


    }

    #region "CS_clsAuth"

    public DataTable GetMyRoles(string strLoginUser)
    {

        SqlParameter[] sqlParams = new SqlParameter[1];

        sqlParams[0] = new SqlParameter("@LoginUser", SqlDbType.VarChar, 50);
        sqlParams[0].Value = strLoginUser;

        StringBuilder sb = new StringBuilder();

        if (!string.IsNullOrEmpty(strLoginUser))
        {
            //有登入就找其角色
            //若登入者是檢舉人則使用3檢舉人當其角色
            sb.AppendLine("SELECT C.RoleID,C.RoleName ");
            sb.AppendLine("FROM FD_view_user A");
            sb.AppendLine("left join (SELECT distinct * FROM dbo.fn_GetAllPapasByUserID(@LoginUser)) TheRole on 1=1 ");
            sb.AppendLine("inner join FD_Role C on CASE WHEN A.UserType='Person' THEN 3 ELSE TheRole.[Value] END = C.RoleID and A.Active='Y' and A.ID=@LoginUser and C.Active='Y' ");

        }
        else
        {
            //沒有登入就會是 2-匿名者
            sb.AppendLine("SELECT RoleID,RoleName ");
            sb.AppendLine("FROM FD_Role A ");
            sb.AppendLine("WHERE RoleID=2 ");        
        }   

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        if (DS != null && DS.Tables.Count > 0 && DS.Tables[0].Rows.Count>0)
        {
            return DS.Tables[0];
        }
        else
        {
            return null;
        }


    }

    #endregion

    #region "MySlidingMenuGroup.ascx"

    public DataTable GetMyMenu()
    {
        StringBuilder sb = new StringBuilder();

        sb.AppendFormat("SELECT distinct A.* ");
        sb.AppendFormat("FROM FD_Program A ");
        sb.AppendFormat("inner join FD_RoleProgramMapping B on A.ProgramID=B.ProgramID and A.Active='Y' ");
        sb.AppendFormat("inner join FD_Role C on B.RoleID=C.RoleID and C.Active='Y' ");
        sb.AppendFormat("WHERE A.Visible='Y' ");
        sb.AppendFormat("AND (C.RoleID in (SELECT * FROM dbo.UTILfn_Split('{0}',',')))", CS_clsAuth.GetMyRoleIDs());

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), null);

        if (DS != null && DS.Tables.Count > 0)
        {
            return DS.Tables[0];
        }
        else
        {
            return null;
        }


    }

    #endregion




    #region "MyEmployeeData"

    //SELECT
    public DataTable MyEmployeeData_Select(string EmployeeID)
    {
        SqlParameter[] sqlParams = new SqlParameter[1];

        sqlParams[0] = new SqlParameter("@EmployeeID", SqlDbType.NVarChar,50);
        sqlParams[0].Value = EmployeeID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("SELECT A.EmployeeID,A.EmployeeName,A.Password,A.Birthday ");
        sb.AppendLine("FROM FD_Employee A ");      
        sb.AppendLine("WHERE 1=1 ");
        sb.AppendLine("AND A.EmployeeID = @EmployeeID ");    

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        return DS.Tables[0];

    }

    //UPDATE
    public Int32 MyEmployeeData_Update(string original_EmployeeID, string EmployeeName, string Password, string Birthday)
    {
        SqlParameter[] sqlParams = new SqlParameter[4];

        sqlParams[0] = new SqlParameter("@EmployeeID", SqlDbType.NVarChar, 50);
        sqlParams[0].Value = original_EmployeeID;
        sqlParams[1] = new SqlParameter("@EmployeeName", SqlDbType.NVarChar, 50);
        sqlParams[1].Value = EmployeeName;
        sqlParams[2] = new SqlParameter("@Password", SqlDbType.NVarChar, 50);
        sqlParams[2].Value = Password;
        sqlParams[3] = new SqlParameter("@Birthday", SqlDbType.NVarChar, 50);
        sqlParams[3].Value = Birthday;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("UPDATE FD_Employee ");
        sb.AppendLine("SET [EmployeeName]=@EmployeeName ");
        sb.AppendLine(",[Password]=@Password ");
        sb.AppendLine(",[Birthday]=@Birthday ");
        sb.AppendLine("WHERE EmployeeID=@EmployeeID ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);
    }
    #endregion 




    

    #region "統計"
    public DataTable CaseReportFormCompute(string StartDate, string EndDate, string ReportType, string OrganizationID)
    {
        SqlParameter[] sqlParams = new SqlParameter[4];

        sqlParams[0] = new SqlParameter("@StartDate", SqlDbType.NVarChar, 10);
        sqlParams[0].Value = StartDate;
        sqlParams[1] = new SqlParameter("@EndDate", SqlDbType.NVarChar, 10);
        sqlParams[1].Value = EndDate;
        sqlParams[2] = new SqlParameter("@ReportType", SqlDbType.NVarChar, 10);
        sqlParams[2].Value = ReportType;
        sqlParams[3] = new SqlParameter("@OrganizationID", SqlDbType.Int);
        sqlParams[3].Value = OrganizationID;

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, "sp_Select_CaseReportFormCompute", sqlParams);

        if (DS != null && DS.Tables.Count > 0 && DS.Tables[0].Rows.Count > 0 )
        {
            return DS.Tables[0];
        }
        else
        {
            return null;
        }
    }

      
    #endregion 

    #region "月報表相關"

    //查詢所有公所的名稱
    public DataTable FD_Organization_Select()
    {
        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Select OrganizationID,OrganizationName From Organization");
        sb.AppendLine(" Where Parent = '15' And Organizationid <> '2' ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString());

        return DS.Tables[0];
    }

    public DataTable FD_OrganizationName_Select()
    {
        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Select Substring(OrganizationName,1,2) From Organization");
        sb.AppendLine(" Where Parent = '15' And Organizationid <> '2' ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString());

        return DS.Tables[0];
    }

    //查詢所有公所在某年某月的資料
    public DataTable FD_MonthForm_Select(String OrganizationID, String Year, String Month)
    {
        SqlParameter[] sqlParams = new SqlParameter[3];

        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.Int);
        sqlParams[0].Value = OrganizationID;
        sqlParams[1] = new SqlParameter("@Year", SqlDbType.Int);
        sqlParams[1].Value = Year;
        sqlParams[2] = new SqlParameter("@Month", SqlDbType.Int);
        sqlParams[2].Value = Month;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine("Select * From FD_MonthForm ");
        sb.AppendLine("Where OrganizationID = @OrganizationID ");
        sb.AppendLine("And [Year] = @Year ");
        sb.AppendLine("and [Month] = @Month ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);


        return DS.Tables[0];
    }


    public int FD_MonthForm_Insert(String OrganizationID, String Year, String Month, String DengueClear1,
        String DengueClear2, String DengueClear3, String DengueClear4, String DengueClear5, String DengueClear6,
        String DengueClear7, String DengueClear8, String DengueClear9, String DengueClearDate, String DengueClearPlace,
        String DenguePublic1, String DenguePublic2, String DenguePublic3, String DenguePublic4, String DenguePublic5,
        String DenguePublic6, String DenguePublicDate, String DenguePublicPlace, String DengueMedication1,
        String DengueMedication2, String DengueMedicationDate, String DengueMedicationPlace, String DengueSer1,
        String DengueSer2, String DengueSer3, String DengueSer4, String DengueSerDate, String DengueSerPlace, String DengueStatus,
        String DitchMember, String DitchDate, String DitchFutureLength, String DitchRealLength, String DitchStatus, String FilthQuantity, String FilthStatus,
        String AdSQuantity, String AdSClearQuantity, String AdSPeople, String AdStatus, String AdLQuantity, String AdLClearQuantity, String AdLStatus,
        String TAction1Report, String TAction1Exhort, String TAction1Catch, String TAction2Report, String TAction2Exhort,
        String TAction3Report, String TAction3Exhort, String TAction4Report, String TAction4Exhort, String TAction5Report,
        String TAction5Exhort, String TAction6Quantity, String TAction7Report, String TAction7Exhort, String TAction8Report,
        String TAction8Exhort, String TAction8Ser, String TStatus, String BTfurniture1, String BTfurniture2,
        String BTfurniture3, String BTfurniture4, String BTfurniture5, String BTfurniture6, String BTsofa1, String BTsofa2,
        String BTsofa3, String BTsofa4, String BTsofa5, String BTsofa6, String BTbed1, String BTbed2, String BTbed3,
        String BTbed4, String BTbed5, String BTbed6, String BTbicycle1, String BTbicycle2, String BTbicycle3,
        String BTbicycle4, String BTbicycle5, String BTbicycle6, String BTtree1, String BTtree2, String BTtree3,
        String BTtree4, String BTtree5, String BTtree6, String BTother1, String BTother2, String BTother3, String BTother4,
        String BTother5, String BTother6, String BTaccount1, String BTaccount2, String BTaccount3, String BTaccount4,
        String BTaccount5, String BTaccount6, String BTStatus, String ACInEmp, String ACOutEmp, String ACSerNum,
        String ACFineNum, String ACHaveFineNum, String ACFineAccount, String ACComplainAccount, String ACUnitAccount,
        String ACVirtualMoney, String ACRealMoney, String ACTotalNum, String ACTotalMoney, String ACPass1Num,
        String ACPass2Num, String ACPass2Money, String ACCompleteNum, String ACCompleteMoney, String ACCertificateNum,
        String ACCertificateMoney, String ACStatus, String State)
    {
        SqlParameter[] sqlParams = new SqlParameter[129];

        //辦理登革熱病媒蚊防治工作紀錄
        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.Int);
        sqlParams[0].Value = OrganizationID;
        sqlParams[1] = new SqlParameter("@Year", SqlDbType.Int);
        sqlParams[1].Value = Year;
        sqlParams[2] = new SqlParameter("@Month", SqlDbType.Int);
        sqlParams[2].Value = Month;
        sqlParams[3] = new SqlParameter("@DengueClear1", SqlDbType.Int);
        sqlParams[3].Value = DengueClear1;
        sqlParams[4] = new SqlParameter("@DengueClear2", SqlDbType.Int);
        sqlParams[4].Value = DengueClear2;
        sqlParams[5] = new SqlParameter("@DengueClear3", SqlDbType.Int);
        sqlParams[5].Value = DengueClear3;
        sqlParams[6] = new SqlParameter("@DengueClear4", SqlDbType.Int);
        sqlParams[6].Value = DengueClear4;
        sqlParams[7] = new SqlParameter("@DengueClear5", SqlDbType.Int);
        sqlParams[7].Value = DengueClear5;
        sqlParams[8] = new SqlParameter("@DengueClear6", SqlDbType.Int);
        sqlParams[8].Value = DengueClear6;
        sqlParams[9] = new SqlParameter("@DengueClear7", SqlDbType.Int);
        sqlParams[9].Value = DengueClear7;
        sqlParams[10] = new SqlParameter("@DengueClear8", SqlDbType.Int);
        sqlParams[10].Value = DengueClear8;
        sqlParams[11] = new SqlParameter("@DengueClear9", SqlDbType.Int);
        sqlParams[11].Value = DengueClear9;
        sqlParams[12] = new SqlParameter("@DengueClearDate", SqlDbType.DateTime);
        sqlParams[12].Value = DengueClearDate;
        sqlParams[13] = new SqlParameter("@DengueClearPlace", SqlDbType.NVarChar, 50);
        sqlParams[13].Value = DengueClearPlace;
        sqlParams[14] = new SqlParameter("@DenguePublic1", SqlDbType.Int);
        sqlParams[14].Value = DenguePublic1;
        sqlParams[15] = new SqlParameter("@DenguePublic2", SqlDbType.Int);
        sqlParams[15].Value = DenguePublic2;
        sqlParams[16] = new SqlParameter("@DenguePublic3", SqlDbType.Int);
        sqlParams[16].Value = DenguePublic3;
        sqlParams[17] = new SqlParameter("@DenguePublic4", SqlDbType.Int);
        sqlParams[17].Value = DenguePublic4;
        sqlParams[18] = new SqlParameter("@DenguePublic5", SqlDbType.Int);
        sqlParams[18].Value = DenguePublic5;
        sqlParams[19] = new SqlParameter("@DenguePublic6", SqlDbType.Int);
        sqlParams[19].Value = DenguePublic6;
        sqlParams[20] = new SqlParameter("@DenguePublicDate", SqlDbType.DateTime);
        sqlParams[20].Value = DenguePublicDate;
        sqlParams[21] = new SqlParameter("@DenguePublicPlace", SqlDbType.NVarChar, 50);
        sqlParams[21].Value = DenguePublicPlace;
        sqlParams[22] = new SqlParameter("@DengueMedication1", SqlDbType.Int);
        sqlParams[22].Value = DengueMedication1;
        sqlParams[23] = new SqlParameter("@DengueMedication2", SqlDbType.Int);
        sqlParams[23].Value = DengueMedication2;
        sqlParams[24] = new SqlParameter("@DengueMedicationDate", SqlDbType.DateTime);
        sqlParams[24].Value = DengueMedicationDate;
        sqlParams[25] = new SqlParameter("@DengueMedicationPlace", SqlDbType.NVarChar, 50);
        sqlParams[25].Value = DengueMedicationPlace;
        sqlParams[26] = new SqlParameter("@DengueSer1", SqlDbType.Int);
        sqlParams[26].Value = DengueSer1;
        sqlParams[27] = new SqlParameter("@DengueSer2", SqlDbType.Int);
        sqlParams[27].Value = DengueSer2;
        sqlParams[28] = new SqlParameter("@DengueSer3", SqlDbType.Int);
        sqlParams[28].Value = DengueSer3;
        sqlParams[29] = new SqlParameter("@DengueSer4", SqlDbType.Int);
        sqlParams[29].Value = DengueSer4;
        sqlParams[30] = new SqlParameter("@DengueSerDate", SqlDbType.DateTime);
        sqlParams[30].Value = DengueSerDate;
        sqlParams[31] = new SqlParameter("@DengueSerPlace", SqlDbType.NVarChar, 50);
        sqlParams[31].Value = DengueSerPlace;
        sqlParams[32] = new SqlParameter("@DengueStatus", SqlDbType.NChar, 1);
        sqlParams[32].Value = DengueStatus;

        //側溝清理作業月報表
        sqlParams[33] = new SqlParameter("@DitchMember", SqlDbType.Int);
        sqlParams[33].Value = DitchMember;
        sqlParams[34] = new SqlParameter("@DitchDate", SqlDbType.Int);
        sqlParams[34].Value = DitchDate;
        sqlParams[35] = new SqlParameter("@DitchFutureLength", SqlDbType.Int);
        sqlParams[35].Value = DitchFutureLength;
        sqlParams[36] = new SqlParameter("@DitchRealLength", SqlDbType.Int);
        sqlParams[36].Value = DitchRealLength;
        sqlParams[37] = new SqlParameter("@DitchStatus", SqlDbType.NChar, 1);
        sqlParams[37].Value = DitchStatus;

        //本月水肥清運量(公噸)
        sqlParams[38] = new SqlParameter("@FilthQuantity", SqlDbType.Int);
        sqlParams[38].Value = FilthQuantity;
        sqlParams[39] = new SqlParameter("@FilthStatus", SqlDbType.NChar);
        sqlParams[39].Value = FilthStatus;

        //取締違規張貼小廣告成果月報表
        sqlParams[40] = new SqlParameter("@AdSQuantity", SqlDbType.Int);
        sqlParams[40].Value = AdSQuantity;
        sqlParams[41] = new SqlParameter("@AdSClearQuantity", SqlDbType.Int);
        sqlParams[41].Value = AdSClearQuantity;
        sqlParams[42] = new SqlParameter("@AdSPeople", SqlDbType.Int);
        sqlParams[42].Value = AdSPeople;
        sqlParams[43] = new SqlParameter("@AdStatus", SqlDbType.NChar, 1);
        sqlParams[43].Value = AdStatus;

        //取締違規設置大型廣告看板成果月報表
        sqlParams[44] = new SqlParameter("@AdLQuantity", SqlDbType.Int);
        sqlParams[44].Value = AdLQuantity;
        sqlParams[45] = new SqlParameter("@AdLClearQuantity", SqlDbType.Int);
        sqlParams[45].Value = AdLClearQuantity;
        sqlParams[46] = new SqlParameter("@AdLStatus", SqlDbType.NChar, 1);
        sqlParams[46].Value = AdLStatus;

        //執行亂丟垃圾相關行為告發及工廁檢查統計表
        sqlParams[47] = new SqlParameter("@TAction1Report", SqlDbType.Int);
        sqlParams[47].Value = TAction1Report;
        sqlParams[48] = new SqlParameter("@TAction1Exhort", SqlDbType.Int);
        sqlParams[48].Value = TAction1Exhort;
        sqlParams[49] = new SqlParameter("@TAction1Catch", SqlDbType.Int);
        sqlParams[49].Value = TAction1Catch;
        sqlParams[50] = new SqlParameter("@TAction2Report", SqlDbType.Int);
        sqlParams[50].Value = TAction2Report;
        sqlParams[51] = new SqlParameter("@TAction2Exhort", SqlDbType.Int);
        sqlParams[51].Value = TAction2Exhort;
        sqlParams[52] = new SqlParameter("@TAction3Report", SqlDbType.Int);
        sqlParams[52].Value = TAction3Report;
        sqlParams[53] = new SqlParameter("@TAction3Exhort", SqlDbType.Int);
        sqlParams[53].Value = TAction3Exhort;
        sqlParams[54] = new SqlParameter("@TAction4Report", SqlDbType.Int);
        sqlParams[54].Value = TAction4Report;
        sqlParams[55] = new SqlParameter("@TAction4Exhort", SqlDbType.Int);
        sqlParams[55].Value = TAction4Exhort;
        sqlParams[56] = new SqlParameter("@TAction5Report", SqlDbType.Int);
        sqlParams[56].Value = TAction5Report;
        sqlParams[57] = new SqlParameter("@TAction5Exhort", SqlDbType.Int);
        sqlParams[57].Value = TAction5Exhort;
        sqlParams[58] = new SqlParameter("@TAction6Quantity", SqlDbType.Int);
        sqlParams[58].Value = TAction6Quantity;
        sqlParams[59] = new SqlParameter("@TAction7Report", SqlDbType.Int);
        sqlParams[59].Value = TAction7Report;
        sqlParams[60] = new SqlParameter("@TAction7Exhort", SqlDbType.Int);
        sqlParams[60].Value = TAction7Exhort;
        sqlParams[61] = new SqlParameter("@TAction8Report", SqlDbType.Int);
        sqlParams[61].Value = TAction8Report;
        sqlParams[62] = new SqlParameter("@TAction8Exhort", SqlDbType.Int);
        sqlParams[62].Value = TAction8Exhort;
        sqlParams[63] = new SqlParameter("@TAction8Ser", SqlDbType.Int);
        sqlParams[63].Value = TAction8Ser;
        sqlParams[64] = new SqlParameter("@TStatus", SqlDbType.NChar, 1);
        sqlParams[64].Value = TStatus;

        //巨大垃圾回收再利用狀況
        sqlParams[65] = new SqlParameter("@BTfurniture1", SqlDbType.Int);
        sqlParams[65].Value = BTfurniture1;
        sqlParams[66] = new SqlParameter("@BTfurniture2", SqlDbType.Int);
        sqlParams[66].Value = BTfurniture2;
        sqlParams[67] = new SqlParameter("@BTfurniture3", SqlDbType.Int);
        sqlParams[67].Value = BTfurniture3;
        sqlParams[68] = new SqlParameter("@BTfurniture4", SqlDbType.Int);
        sqlParams[68].Value = BTfurniture4;
        sqlParams[69] = new SqlParameter("@BTfurniture5", SqlDbType.Int);
        sqlParams[69].Value = BTfurniture5;
        sqlParams[70] = new SqlParameter("@BTfurniture6", SqlDbType.Int);
        sqlParams[70].Value = BTfurniture6;
        sqlParams[71] = new SqlParameter("@BTsofa1", SqlDbType.Int);
        sqlParams[71].Value = BTsofa1;
        sqlParams[72] = new SqlParameter("@BTsofa2", SqlDbType.Int);
        sqlParams[72].Value = BTsofa2;
        sqlParams[73] = new SqlParameter("@BTsofa3", SqlDbType.Int);
        sqlParams[73].Value = BTsofa3;
        sqlParams[74] = new SqlParameter("@BTsofa4", SqlDbType.Int);
        sqlParams[74].Value = BTsofa4;
        sqlParams[75] = new SqlParameter("@BTsofa5", SqlDbType.Int);
        sqlParams[75].Value = BTsofa5;
        sqlParams[76] = new SqlParameter("@BTsofa6", SqlDbType.Int);
        sqlParams[76].Value = BTsofa6;
        sqlParams[77] = new SqlParameter("@BTbed1", SqlDbType.Int);
        sqlParams[77].Value = BTbed1;
        sqlParams[78] = new SqlParameter("@BTbed2", SqlDbType.Int);
        sqlParams[78].Value = BTbed2;
        sqlParams[79] = new SqlParameter("@BTbed3", SqlDbType.Int);
        sqlParams[79].Value = BTbed3;
        sqlParams[80] = new SqlParameter("@BTbed4", SqlDbType.Int);
        sqlParams[80].Value = BTbed4;
        sqlParams[81] = new SqlParameter("@BTbed5", SqlDbType.Int);
        sqlParams[81].Value = BTbed5;
        sqlParams[82] = new SqlParameter("@BTbed6", SqlDbType.Int);
        sqlParams[82].Value = BTbed6;
        sqlParams[83] = new SqlParameter("@BTbicycle1", SqlDbType.Int);
        sqlParams[83].Value = BTbicycle1;
        sqlParams[84] = new SqlParameter("@BTbicycle2", SqlDbType.Int);
        sqlParams[84].Value = BTbicycle2;
        sqlParams[85] = new SqlParameter("@BTbicycle3", SqlDbType.Int);
        sqlParams[85].Value = BTbicycle3;
        sqlParams[86] = new SqlParameter("@BTbicycle4", SqlDbType.Int);
        sqlParams[86].Value = BTbicycle4;
        sqlParams[87] = new SqlParameter("@BTbicycle5", SqlDbType.Int);
        sqlParams[87].Value = BTbicycle5;
        sqlParams[88] = new SqlParameter("@BTbicycle6", SqlDbType.Int);
        sqlParams[88].Value = BTbicycle6;
        sqlParams[89] = new SqlParameter("@BTtree1", SqlDbType.Int);
        sqlParams[89].Value = BTtree1;
        sqlParams[90] = new SqlParameter("@BTtree2", SqlDbType.Int);
        sqlParams[90].Value = BTtree2;
        sqlParams[91] = new SqlParameter("@BTtree3", SqlDbType.Int);
        sqlParams[91].Value = BTtree3;
        sqlParams[92] = new SqlParameter("@BTtree4", SqlDbType.Int);
        sqlParams[92].Value = BTtree4;
        sqlParams[93] = new SqlParameter("@BTtree5", SqlDbType.Int);
        sqlParams[93].Value = BTtree5;
        sqlParams[94] = new SqlParameter("@BTtree6", SqlDbType.Int);
        sqlParams[94].Value = BTtree6;
        sqlParams[95] = new SqlParameter("@BTother1", SqlDbType.Int);
        sqlParams[95].Value = BTother1;
        sqlParams[96] = new SqlParameter("@BTother2", SqlDbType.Int);
        sqlParams[96].Value = BTother2;
        sqlParams[97] = new SqlParameter("@BTother3", SqlDbType.Int);
        sqlParams[97].Value = BTother3;
        sqlParams[98] = new SqlParameter("@BTother4", SqlDbType.Int);
        sqlParams[98].Value = BTother4;
        sqlParams[99] = new SqlParameter("@BTother5", SqlDbType.Int);
        sqlParams[99].Value = BTother5;
        sqlParams[100] = new SqlParameter("@BTother6", SqlDbType.Int);
        sqlParams[100].Value = BTother6;
        sqlParams[101] = new SqlParameter("@BTaccount1", SqlDbType.Int);
        sqlParams[101].Value = BTaccount1;
        sqlParams[102] = new SqlParameter("@BTaccount2", SqlDbType.Int);
        sqlParams[102].Value = BTaccount2;
        sqlParams[103] = new SqlParameter("@BTaccount3", SqlDbType.Int);
        sqlParams[103].Value = BTaccount3;
        sqlParams[104] = new SqlParameter("@BTaccount4", SqlDbType.Int);
        sqlParams[104].Value = BTaccount4;
        sqlParams[105] = new SqlParameter("@BTaccount5", SqlDbType.Int);
        sqlParams[105].Value = BTaccount5;
        sqlParams[106] = new SqlParameter("@BTaccount6", SqlDbType.Int);
        sqlParams[106].Value = BTaccount6;
        sqlParams[107] = new SqlParameter("@BTStatus", SqlDbType.NChar, 1);
        sqlParams[107].Value = BTStatus;

        //廢棄物污染源稽查處分概況
        sqlParams[108] = new SqlParameter("@ACInEmp", SqlDbType.Int);
        sqlParams[108].Value = ACInEmp;
        sqlParams[109] = new SqlParameter("@ACOutEmp", SqlDbType.Int);
        sqlParams[109].Value = ACOutEmp;
        sqlParams[110] = new SqlParameter("@ACSerNum", SqlDbType.Int);
        sqlParams[110].Value = ACSerNum;
        sqlParams[111] = new SqlParameter("@ACFineNum", SqlDbType.Int);
        sqlParams[111].Value = ACFineNum;
        sqlParams[112] = new SqlParameter("@ACHaveFineNum", SqlDbType.Int);
        sqlParams[112].Value = ACHaveFineNum;
        sqlParams[113] = new SqlParameter("@ACFineAccount", SqlDbType.Int);
        sqlParams[113].Value = ACFineAccount;
        sqlParams[114] = new SqlParameter("@ACComplainAccount", SqlDbType.Int);
        sqlParams[114].Value = ACComplainAccount;
        sqlParams[115] = new SqlParameter("@ACUnitAccount", SqlDbType.Int);
        sqlParams[115].Value = ACUnitAccount;
        sqlParams[116] = new SqlParameter("@ACVirtualMoney", SqlDbType.Int);
        sqlParams[116].Value = ACVirtualMoney;
        sqlParams[117] = new SqlParameter("@ACRealMoney", SqlDbType.Int);
        sqlParams[117].Value = ACRealMoney;
        sqlParams[118] = new SqlParameter("@ACTotalNum", SqlDbType.Int);
        sqlParams[118].Value = ACTotalNum;
        sqlParams[119] = new SqlParameter("@ACTotalMoney", SqlDbType.Int);
        sqlParams[119].Value = ACTotalMoney;
        sqlParams[120] = new SqlParameter("@ACPass1Num", SqlDbType.Int);
        sqlParams[120].Value = ACPass1Num;
        sqlParams[121] = new SqlParameter("@ACPass2Num", SqlDbType.Int);
        sqlParams[121].Value = ACPass2Num;
        sqlParams[122] = new SqlParameter("@ACPass2Money", SqlDbType.Int);
        sqlParams[122].Value = ACPass2Money;
        sqlParams[123] = new SqlParameter("@ACCompleteNum", SqlDbType.Int);
        sqlParams[123].Value = ACCompleteNum;
        sqlParams[124] = new SqlParameter("@ACCompleteMoney", SqlDbType.Int);
        sqlParams[124].Value = ACCompleteMoney;
        sqlParams[125] = new SqlParameter("@ACCertificateNum", SqlDbType.Int);
        sqlParams[125].Value = ACCertificateNum;
        sqlParams[126] = new SqlParameter("@ACCertificateMoney", SqlDbType.Int);
        sqlParams[126].Value = ACCertificateMoney;
        sqlParams[127] = new SqlParameter("@ACStatus", SqlDbType.NChar, 1);
        sqlParams[127].Value = ACStatus;
        sqlParams[128] = new SqlParameter("@State", SqlDbType.NVarChar, 200);
        sqlParams[128].Value = State;

        StringBuilder sb = new StringBuilder();

        //辦理登革熱病媒蚊防治工作紀錄參數  --33
        sb.AppendLine(" Insert Into FD_MonthForm ");
        sb.AppendLine(" values(@OrganizationID,@Year,@Month,@DengueClear1,@DengueClear2,@DengueClear3, ");
        sb.AppendLine(" @DengueClear4,@DengueClear5,@DengueClear6,@DengueClear7,@DengueClear8,@DengueClear9, ");
        sb.AppendLine(" @DengueClearDate,@DengueClearPlace,@DenguePublic1,@DenguePublic2,@DenguePublic3,@DenguePublic4, ");
        sb.AppendLine(" @DenguePublic5,@DenguePublic6,@DenguePublicDate,@DenguePublicPlace,@DengueMedication1,@DengueMedication2, ");
        sb.AppendLine(" @DengueMedicationDate,@DengueMedicationPlace,@DengueSer1,@DengueSer2,@DengueSer3,@DengueSer4, ");
        sb.AppendLine(" @DengueSerDate,@DengueSerPlace,@DengueStatus, ");

        //側溝清理作業月報表參數  --5
        sb.AppendLine(" @DitchMember,@DitchDate,@DitchFutureLength,@DitchRealLength,@DitchStatus, ");

        //本月水肥清運量(公噸)參數 --2
        sb.AppendLine(" @FilthQuantity,@FilthStatus, ");

        //取締違規張貼小廣告成果月報表參數 --4
        sb.AppendLine(" @AdSQuantity,@AdSClearQuantity,@AdSPeople,@AdStatus, ");

        //取締違規設置大型廣告看板成果月報表參數 --3
        sb.AppendLine(" @AdLQuantity,@AdLClearQuantity,@AdLStatus, ");

        //執行亂丟垃圾相關行為告發及工廁檢查統計表參數 --18
        sb.AppendLine(" @TAction1Report,@TAction1Exhort,@TAction1Catch,@TAction2Report,@TAction2Exhort,@TAction3Report, ");
        sb.AppendLine(" @TAction3Exhort,@TAction4Report,@TAction4Exhort,@TAction5Report,@TAction5Exhort,@TAction6Quantity, ");
        sb.AppendLine(" @TAction7Report,@TAction7Exhort,@TAction8Report,@TAction8Exhort,@TAction8Ser,@TStatus, ");

        //巨大垃圾回收再利用狀況參數 --43
        sb.AppendLine(" @BTfurniture1,@BTfurniture2,@BTfurniture3,@BTfurniture4,@BTfurniture5,@BTfurniture6, ");
        sb.AppendLine(" @BTsofa1,@BTsofa2,@BTsofa3,@BTsofa4,@BTsofa5,@BTsofa6, ");
        sb.AppendLine(" @BTbed1,@BTbed2,@BTbed3,@BTbed4,@BTbed5,@BTbed6, ");
        sb.AppendLine(" @BTbicycle1,@BTbicycle2,@BTbicycle3,@BTbicycle4,@BTbicycle5,@BTbicycle6, ");
        sb.AppendLine(" @BTtree1,@BTtree2,@BTtree3,@BTtree4,@BTtree5,@BTtree6, ");
        sb.AppendLine(" @BTother1,@BTother2,@BTother3,@BTother4,@BTother5,@BTother6, ");
        sb.AppendLine(" @BTaccount1,@BTaccount2,@BTaccount3,@BTaccount4,@BTaccount5,@BTaccount6, ");
        sb.AppendLine(" @BTStatus, ");

        //廢棄物污染源稽查處分概況參數 --20
        sb.AppendLine(" @ACInEmp,@ACOutEmp,@ACSerNum,@ACFineNum,@ACHaveFineNum,@ACFineAccount, ");
        sb.AppendLine(" @ACComplainAccount,@ACUnitAccount,@ACVirtualMoney,@ACRealMoney,@ACTotalNum,@ACTotalMoney, ");
        sb.AppendLine(" @ACPass1Num,@ACPass2Num,@ACPass2Money,@ACCompleteNum,@ACCompleteMoney,@ACCertificateNum, ");
        sb.AppendLine(" @ACCertificateMoney,@ACStatus, ");

        //處理情形說明
        sb.AppendLine(" @State ) ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);

    }

    public int FD_MonthForm_Update(String OrganizationID, String Year, String Month, String DengueClear1,
        String DengueClear2, String DengueClear3, String DengueClear4, String DengueClear5, String DengueClear6,
        String DengueClear7, String DengueClear8, String DengueClear9, String DengueClearDate, String DengueClearPlace,
        String DenguePublic1, String DenguePublic2, String DenguePublic3, String DenguePublic4, String DenguePublic5,
        String DenguePublic6, String DenguePublicDate, String DenguePublicPlace, String DengueMedication1,
        String DengueMedication2, String DengueMedicationDate, String DengueMedicationPlace, String DengueSer1,
        String DengueSer2, String DengueSer3, String DengueSer4, String DengueSerDate, String DengueSerPlace, String DengueStatus,
        String DitchMember, String DitchDate, String DitchFutureLength, String DitchRealLength, String DitchStatus, String FilthQuantity, String FilthStatus,
        String AdSQuantity, String AdSClearQuantity, String AdSPeople, String AdStatus, String AdLQuantity, String AdLClearQuantity, String AdLStatus,
        String TAction1Report, String TAction1Exhort, String TAction1Catch, String TAction2Report, String TAction2Exhort,
        String TAction3Report, String TAction3Exhort, String TAction4Report, String TAction4Exhort, String TAction5Report,
        String TAction5Exhort, String TAction6Quantity, String TAction7Report, String TAction7Exhort, String TAction8Report,
        String TAction8Exhort, String TAction8Ser, String TStatus, String BTfurniture1, String BTfurniture2,
        String BTfurniture3, String BTfurniture4, String BTfurniture5, String BTfurniture6, String BTsofa1, String BTsofa2,
        String BTsofa3, String BTsofa4, String BTsofa5, String BTsofa6, String BTbed1, String BTbed2, String BTbed3,
        String BTbed4, String BTbed5, String BTbed6, String BTbicycle1, String BTbicycle2, String BTbicycle3,
        String BTbicycle4, String BTbicycle5, String BTbicycle6, String BTtree1, String BTtree2, String BTtree3,
        String BTtree4, String BTtree5, String BTtree6, String BTother1, String BTother2, String BTother3, String BTother4,
        String BTother5, String BTother6, String BTaccount1, String BTaccount2, String BTaccount3, String BTaccount4,
        String BTaccount5, String BTaccount6, String BTStatus, String ACInEmp, String ACOutEmp, String ACSerNum,
        String ACFineNum, String ACHaveFineNum, String ACFineAccount, String ACComplainAccount, String ACUnitAccount,
        String ACVirtualMoney, String ACRealMoney, String ACTotalNum, String ACTotalMoney, String ACPass1Num,
        String ACPass2Num, String ACPass2Money, String ACCompleteNum, String ACCompleteMoney, String ACCertificateNum,
        String ACCertificateMoney, String ACStatus, String State)
    {
        SqlParameter[] sqlParams = new SqlParameter[129];

        //辦理登革熱病媒蚊防治工作紀錄
        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.Int);
        sqlParams[0].Value = OrganizationID;
        sqlParams[1] = new SqlParameter("@Year", SqlDbType.Int);
        sqlParams[1].Value = Year;
        sqlParams[2] = new SqlParameter("@Month", SqlDbType.Int);
        sqlParams[2].Value = Month;
        sqlParams[3] = new SqlParameter("@DengueClear1", SqlDbType.Int);
        sqlParams[3].Value = DengueClear1;
        sqlParams[4] = new SqlParameter("@DengueClear2", SqlDbType.Int);
        sqlParams[4].Value = DengueClear2;
        sqlParams[5] = new SqlParameter("@DengueClear3", SqlDbType.Int);
        sqlParams[5].Value = DengueClear3;
        sqlParams[6] = new SqlParameter("@DengueClear4", SqlDbType.Int);
        sqlParams[6].Value = DengueClear4;
        sqlParams[7] = new SqlParameter("@DengueClear5", SqlDbType.Int);
        sqlParams[7].Value = DengueClear5;
        sqlParams[8] = new SqlParameter("@DengueClear6", SqlDbType.Int);
        sqlParams[8].Value = DengueClear6;
        sqlParams[9] = new SqlParameter("@DengueClear7", SqlDbType.Int);
        sqlParams[9].Value = DengueClear7;
        sqlParams[10] = new SqlParameter("@DengueClear8", SqlDbType.Int);
        sqlParams[10].Value = DengueClear8;
        sqlParams[11] = new SqlParameter("@DengueClear9", SqlDbType.Int);
        sqlParams[11].Value = DengueClear9;
        sqlParams[12] = new SqlParameter("@DengueClearDate", SqlDbType.DateTime);
        sqlParams[12].Value = DengueClearDate;
        sqlParams[13] = new SqlParameter("@DengueClearPlace", SqlDbType.NVarChar, 50);
        sqlParams[13].Value = DengueClearPlace;
        sqlParams[14] = new SqlParameter("@DenguePublic1", SqlDbType.Int);
        sqlParams[14].Value = DenguePublic1;
        sqlParams[15] = new SqlParameter("@DenguePublic2", SqlDbType.Int);
        sqlParams[15].Value = DenguePublic2;
        sqlParams[16] = new SqlParameter("@DenguePublic3", SqlDbType.Int);
        sqlParams[16].Value = DenguePublic3;
        sqlParams[17] = new SqlParameter("@DenguePublic4", SqlDbType.Int);
        sqlParams[17].Value = DenguePublic4;
        sqlParams[18] = new SqlParameter("@DenguePublic5", SqlDbType.Int);
        sqlParams[18].Value = DenguePublic5;
        sqlParams[19] = new SqlParameter("@DenguePublic6", SqlDbType.Int);
        sqlParams[19].Value = DenguePublic6;
        sqlParams[20] = new SqlParameter("@DenguePublicDate", SqlDbType.DateTime);
        sqlParams[20].Value = DenguePublicDate;
        sqlParams[21] = new SqlParameter("@DenguePublicPlace", SqlDbType.NVarChar, 50);
        sqlParams[21].Value = DenguePublicPlace;
        sqlParams[22] = new SqlParameter("@DengueMedication1", SqlDbType.Int);
        sqlParams[22].Value = DengueMedication1;
        sqlParams[23] = new SqlParameter("@DengueMedication2", SqlDbType.Int);
        sqlParams[23].Value = DengueMedication2;
        sqlParams[24] = new SqlParameter("@DengueMedicationDate", SqlDbType.DateTime);
        sqlParams[24].Value = DengueMedicationDate;
        sqlParams[25] = new SqlParameter("@DengueMedicationPlace", SqlDbType.NVarChar, 50);
        sqlParams[25].Value = DengueMedicationPlace;
        sqlParams[26] = new SqlParameter("@DengueSer1", SqlDbType.Int);
        sqlParams[26].Value = DengueSer1;
        sqlParams[27] = new SqlParameter("@DengueSer2", SqlDbType.Int);
        sqlParams[27].Value = DengueSer2;
        sqlParams[28] = new SqlParameter("@DengueSer3", SqlDbType.Int);
        sqlParams[28].Value = DengueSer3;
        sqlParams[29] = new SqlParameter("@DengueSer4", SqlDbType.Int);
        sqlParams[29].Value = DengueSer4;
        sqlParams[30] = new SqlParameter("@DengueSerDate", SqlDbType.DateTime);
        sqlParams[30].Value = DengueSerDate;
        sqlParams[31] = new SqlParameter("@DengueSerPlace", SqlDbType.NVarChar, 50);
        sqlParams[31].Value = DengueSerPlace;
        sqlParams[32] = new SqlParameter("@DengueStatus", SqlDbType.NChar, 1);
        sqlParams[32].Value = DengueStatus;

        //側溝清理作業月報表
        sqlParams[33] = new SqlParameter("@DitchMember", SqlDbType.Int);
        sqlParams[33].Value = DitchMember;
        sqlParams[34] = new SqlParameter("@DitchDate", SqlDbType.Int);
        sqlParams[34].Value = DitchDate;
        sqlParams[35] = new SqlParameter("@DitchFutureLength", SqlDbType.Int);
        sqlParams[35].Value = DitchFutureLength;
        sqlParams[36] = new SqlParameter("@DitchRealLength", SqlDbType.Int);
        sqlParams[36].Value = DitchRealLength;
        sqlParams[37] = new SqlParameter("@DitchStatus", SqlDbType.NChar, 1);
        sqlParams[37].Value = DitchStatus;

        //本月水肥清運量(公噸)
        sqlParams[38] = new SqlParameter("@FilthQuantity", SqlDbType.Int);
        sqlParams[38].Value = FilthQuantity;
        sqlParams[39] = new SqlParameter("@FilthStatus", SqlDbType.NChar);
        sqlParams[39].Value = FilthStatus;

        //取締違規張貼小廣告成果月報表
        sqlParams[40] = new SqlParameter("@AdSQuantity", SqlDbType.Int);
        sqlParams[40].Value = AdSQuantity;
        sqlParams[41] = new SqlParameter("@AdSClearQuantity", SqlDbType.Int);
        sqlParams[41].Value = AdSClearQuantity;
        sqlParams[42] = new SqlParameter("@AdSPeople", SqlDbType.Int);
        sqlParams[42].Value = AdSPeople;
        sqlParams[43] = new SqlParameter("@AdStatus", SqlDbType.NChar, 1);
        sqlParams[43].Value = AdStatus;

        //取締違規設置大型廣告看板成果月報表
        sqlParams[44] = new SqlParameter("@AdLQuantity", SqlDbType.Int);
        sqlParams[44].Value = AdLQuantity;
        sqlParams[45] = new SqlParameter("@AdLClearQuantity", SqlDbType.Int);
        sqlParams[45].Value = AdLClearQuantity;
        sqlParams[46] = new SqlParameter("@AdLStatus", SqlDbType.NChar, 1);
        sqlParams[46].Value = AdLStatus;

        //執行亂丟垃圾相關行為告發及工廁檢查統計表
        sqlParams[47] = new SqlParameter("@TAction1Report", SqlDbType.Int);
        sqlParams[47].Value = TAction1Report;
        sqlParams[48] = new SqlParameter("@TAction1Exhort", SqlDbType.Int);
        sqlParams[48].Value = TAction1Exhort;
        sqlParams[49] = new SqlParameter("@TAction1Catch", SqlDbType.Int);
        sqlParams[49].Value = TAction1Catch;
        sqlParams[50] = new SqlParameter("@TAction2Report", SqlDbType.Int);
        sqlParams[50].Value = TAction2Report;
        sqlParams[51] = new SqlParameter("@TAction2Exhort", SqlDbType.Int);
        sqlParams[51].Value = TAction2Exhort;
        sqlParams[52] = new SqlParameter("@TAction3Report", SqlDbType.Int);
        sqlParams[52].Value = TAction3Report;
        sqlParams[53] = new SqlParameter("@TAction3Exhort", SqlDbType.Int);
        sqlParams[53].Value = TAction3Exhort;
        sqlParams[54] = new SqlParameter("@TAction4Report", SqlDbType.Int);
        sqlParams[54].Value = TAction4Report;
        sqlParams[55] = new SqlParameter("@TAction4Exhort", SqlDbType.Int);
        sqlParams[55].Value = TAction4Exhort;
        sqlParams[56] = new SqlParameter("@TAction5Report", SqlDbType.Int);
        sqlParams[56].Value = TAction5Report;
        sqlParams[57] = new SqlParameter("@TAction5Exhort", SqlDbType.Int);
        sqlParams[57].Value = TAction5Exhort;
        sqlParams[58] = new SqlParameter("@TAction6Quantity", SqlDbType.Int);
        sqlParams[58].Value = TAction6Quantity;
        sqlParams[59] = new SqlParameter("@TAction7Report", SqlDbType.Int);
        sqlParams[59].Value = TAction7Report;
        sqlParams[60] = new SqlParameter("@TAction7Exhort", SqlDbType.Int);
        sqlParams[60].Value = TAction7Exhort;
        sqlParams[61] = new SqlParameter("@TAction8Report", SqlDbType.Int);
        sqlParams[61].Value = TAction8Report;
        sqlParams[62] = new SqlParameter("@TAction8Exhort", SqlDbType.Int);
        sqlParams[62].Value = TAction8Exhort;
        sqlParams[63] = new SqlParameter("@TAction8Ser", SqlDbType.Int);
        sqlParams[63].Value = TAction8Ser;
        sqlParams[64] = new SqlParameter("@TStatus", SqlDbType.NChar, 1);
        sqlParams[64].Value = TStatus;

        //巨大垃圾回收再利用狀況
        sqlParams[65] = new SqlParameter("@BTfurniture1", SqlDbType.Int);
        sqlParams[65].Value = BTfurniture1;
        sqlParams[66] = new SqlParameter("@BTfurniture2", SqlDbType.Int);
        sqlParams[66].Value = BTfurniture2;
        sqlParams[67] = new SqlParameter("@BTfurniture3", SqlDbType.Int);
        sqlParams[67].Value = BTfurniture3;
        sqlParams[68] = new SqlParameter("@BTfurniture4", SqlDbType.Int);
        sqlParams[68].Value = BTfurniture4;
        sqlParams[69] = new SqlParameter("@BTfurniture5", SqlDbType.Int);
        sqlParams[69].Value = BTfurniture5;
        sqlParams[70] = new SqlParameter("@BTfurniture6", SqlDbType.Int);
        sqlParams[70].Value = BTfurniture6;
        sqlParams[71] = new SqlParameter("@BTsofa1", SqlDbType.Int);
        sqlParams[71].Value = BTsofa1;
        sqlParams[72] = new SqlParameter("@BTsofa2", SqlDbType.Int);
        sqlParams[72].Value = BTsofa2;
        sqlParams[73] = new SqlParameter("@BTsofa3", SqlDbType.Int);
        sqlParams[73].Value = BTsofa3;
        sqlParams[74] = new SqlParameter("@BTsofa4", SqlDbType.Int);
        sqlParams[74].Value = BTsofa4;
        sqlParams[75] = new SqlParameter("@BTsofa5", SqlDbType.Int);
        sqlParams[75].Value = BTsofa5;
        sqlParams[76] = new SqlParameter("@BTsofa6", SqlDbType.Int);
        sqlParams[76].Value = BTsofa6;
        sqlParams[77] = new SqlParameter("@BTbed1", SqlDbType.Int);
        sqlParams[77].Value = BTbed1;
        sqlParams[78] = new SqlParameter("@BTbed2", SqlDbType.Int);
        sqlParams[78].Value = BTbed2;
        sqlParams[79] = new SqlParameter("@BTbed3", SqlDbType.Int);
        sqlParams[79].Value = BTbed3;
        sqlParams[80] = new SqlParameter("@BTbed4", SqlDbType.Int);
        sqlParams[80].Value = BTbed4;
        sqlParams[81] = new SqlParameter("@BTbed5", SqlDbType.Int);
        sqlParams[81].Value = BTbed5;
        sqlParams[82] = new SqlParameter("@BTbed6", SqlDbType.Int);
        sqlParams[82].Value = BTbed6;
        sqlParams[83] = new SqlParameter("@BTbicycle1", SqlDbType.Int);
        sqlParams[83].Value = BTbicycle1;
        sqlParams[84] = new SqlParameter("@BTbicycle2", SqlDbType.Int);
        sqlParams[84].Value = BTbicycle2;
        sqlParams[85] = new SqlParameter("@BTbicycle3", SqlDbType.Int);
        sqlParams[85].Value = BTbicycle3;
        sqlParams[86] = new SqlParameter("@BTbicycle4", SqlDbType.Int);
        sqlParams[86].Value = BTbicycle4;
        sqlParams[87] = new SqlParameter("@BTbicycle5", SqlDbType.Int);
        sqlParams[87].Value = BTbicycle5;
        sqlParams[88] = new SqlParameter("@BTbicycle6", SqlDbType.Int);
        sqlParams[88].Value = BTbicycle6;
        sqlParams[89] = new SqlParameter("@BTtree1", SqlDbType.Int);
        sqlParams[89].Value = BTtree1;
        sqlParams[90] = new SqlParameter("@BTtree2", SqlDbType.Int);
        sqlParams[90].Value = BTtree2;
        sqlParams[91] = new SqlParameter("@BTtree3", SqlDbType.Int);
        sqlParams[91].Value = BTtree3;
        sqlParams[92] = new SqlParameter("@BTtree4", SqlDbType.Int);
        sqlParams[92].Value = BTtree4;
        sqlParams[93] = new SqlParameter("@BTtree5", SqlDbType.Int);
        sqlParams[93].Value = BTtree5;
        sqlParams[94] = new SqlParameter("@BTtree6", SqlDbType.Int);
        sqlParams[94].Value = BTtree6;
        sqlParams[95] = new SqlParameter("@BTother1", SqlDbType.Int);
        sqlParams[95].Value = BTother1;
        sqlParams[96] = new SqlParameter("@BTother2", SqlDbType.Int);
        sqlParams[96].Value = BTother2;
        sqlParams[97] = new SqlParameter("@BTother3", SqlDbType.Int);
        sqlParams[97].Value = BTother3;
        sqlParams[98] = new SqlParameter("@BTother4", SqlDbType.Int);
        sqlParams[98].Value = BTother4;
        sqlParams[99] = new SqlParameter("@BTother5", SqlDbType.Int);
        sqlParams[99].Value = BTother5;
        sqlParams[100] = new SqlParameter("@BTother6", SqlDbType.Int);
        sqlParams[100].Value = BTother6;
        sqlParams[101] = new SqlParameter("@BTaccount1", SqlDbType.Int);
        sqlParams[101].Value = BTaccount1;
        sqlParams[102] = new SqlParameter("@BTaccount2", SqlDbType.Int);
        sqlParams[102].Value = BTaccount2;
        sqlParams[103] = new SqlParameter("@BTaccount3", SqlDbType.Int);
        sqlParams[103].Value = BTaccount3;
        sqlParams[104] = new SqlParameter("@BTaccount4", SqlDbType.Int);
        sqlParams[104].Value = BTaccount4;
        sqlParams[105] = new SqlParameter("@BTaccount5", SqlDbType.Int);
        sqlParams[105].Value = BTaccount5;
        sqlParams[106] = new SqlParameter("@BTaccount6", SqlDbType.Int);
        sqlParams[106].Value = BTaccount6;
        sqlParams[107] = new SqlParameter("@BTStatus", SqlDbType.NChar, 1);
        sqlParams[107].Value = BTStatus;

        //廢棄物污染源稽查處分概況
        sqlParams[108] = new SqlParameter("@ACInEmp", SqlDbType.Int);
        sqlParams[108].Value = ACInEmp;
        sqlParams[109] = new SqlParameter("@ACOutEmp", SqlDbType.Int);
        sqlParams[109].Value = ACOutEmp;
        sqlParams[110] = new SqlParameter("@ACSerNum", SqlDbType.Int);
        sqlParams[110].Value = ACSerNum;
        sqlParams[111] = new SqlParameter("@ACFineNum", SqlDbType.Int);
        sqlParams[111].Value = ACFineNum;
        sqlParams[112] = new SqlParameter("@ACHaveFineNum", SqlDbType.Int);
        sqlParams[112].Value = ACHaveFineNum;
        sqlParams[113] = new SqlParameter("@ACFineAccount", SqlDbType.Int);
        sqlParams[113].Value = ACFineAccount;
        sqlParams[114] = new SqlParameter("@ACComplainAccount", SqlDbType.Int);
        sqlParams[114].Value = ACComplainAccount;
        sqlParams[115] = new SqlParameter("@ACUnitAccount", SqlDbType.Int);
        sqlParams[115].Value = ACUnitAccount;
        sqlParams[116] = new SqlParameter("@ACVirtualMoney", SqlDbType.Int);
        sqlParams[116].Value = ACVirtualMoney;
        sqlParams[117] = new SqlParameter("@ACRealMoney", SqlDbType.Int);
        sqlParams[117].Value = ACRealMoney;
        sqlParams[118] = new SqlParameter("@ACTotalNum", SqlDbType.Int);
        sqlParams[118].Value = ACTotalNum;
        sqlParams[119] = new SqlParameter("@ACTotalMoney", SqlDbType.Int);
        sqlParams[119].Value = ACTotalMoney;
        sqlParams[120] = new SqlParameter("@ACPass1Num", SqlDbType.Int);
        sqlParams[120].Value = ACPass1Num;
        sqlParams[121] = new SqlParameter("@ACPass2Num", SqlDbType.Int);
        sqlParams[121].Value = ACPass2Num;
        sqlParams[122] = new SqlParameter("@ACPass2Money", SqlDbType.Int);
        sqlParams[122].Value = ACPass2Money;
        sqlParams[123] = new SqlParameter("@ACCompleteNum", SqlDbType.Int);
        sqlParams[123].Value = ACCompleteNum;
        sqlParams[124] = new SqlParameter("@ACCompleteMoney", SqlDbType.Int);
        sqlParams[124].Value = ACCompleteMoney;
        sqlParams[125] = new SqlParameter("@ACCertificateNum", SqlDbType.Int);
        sqlParams[125].Value = ACCertificateNum;
        sqlParams[126] = new SqlParameter("@ACCertificateMoney", SqlDbType.Int);
        sqlParams[126].Value = ACCertificateMoney;
        sqlParams[127] = new SqlParameter("@ACStatus", SqlDbType.NChar, 1);
        sqlParams[127].Value = ACStatus;

        //處理情形說明
        sqlParams[128] = new SqlParameter("@State", SqlDbType.NVarChar, 200);
        sqlParams[128].Value = State;

        StringBuilder sb = new StringBuilder();

        //辦理登革熱病媒蚊防治工作紀錄參數  --33
        sb.AppendLine(" Update FD_MonthForm ");
        sb.AppendLine(" Set OrganizationID=@OrganizationID,Year=@Year,Month=@Month,DengueClear1=@DengueClear1,DengueClear2=@DengueClear2,DengueClear3=@DengueClear3, ");
        sb.AppendLine(" DengueClear4=@DengueClear4,DengueClear5=@DengueClear5,DengueClear6=@DengueClear6,DengueClear7=@DengueClear7,DengueClear8=@DengueClear8,DengueClear9=@DengueClear9, ");
        sb.AppendLine(" DengueClearDate=@DengueClearDate,DengueClearPlace=@DengueClearPlace,DenguePublic1=@DenguePublic1,DenguePublic2=@DenguePublic2,DenguePublic3=@DenguePublic3,DenguePublic4=@DenguePublic4, ");
        sb.AppendLine(" DenguePublic5=@DenguePublic5,DenguePublic6=@DenguePublic6,DenguePublicDate=@DenguePublicDate,DenguePublicPlace=@DenguePublicPlace,DengueMedication1=@DengueMedication1,DengueMedication2=@DengueMedication2, ");
        sb.AppendLine(" DengueMedicationDate=@DengueMedicationDate,DengueMedicationPlace=@DengueMedicationPlace,DengueSer1=@DengueSer1,DengueSer2=@DengueSer2,DengueSer3=@DengueSer3,DengueSer4=@DengueSer4, ");
        sb.AppendLine(" DengueSerDate=@DengueSerDate,DengueSerPlace=@DengueSerPlace,DengueStatus=@DengueStatus, ");

        //側溝清理作業月報表參數  --5
        sb.AppendLine(" DitchMember=@DitchMember,DitchDate=@DitchDate,DitchFutureLength=@DitchFutureLength,DitchRealLength=@DitchRealLength,DitchStatus=@DitchStatus, ");

        //本月水肥清運量(公噸)參數 --2
        sb.AppendLine(" FilthQuantity=@FilthQuantity,FilthStatus=@FilthStatus, ");

        //取締違規張貼小廣告成果月報表參數 --4
        sb.AppendLine(" AdSQuantity=@AdSQuantity,AdSClearQuantity=@AdSClearQuantity,AdSPeople=@AdSPeople,AdStatus=@AdStatus, ");

        //取締違規設置大型廣告看板成果月報表參數 --3
        sb.AppendLine(" AdLQuantity=@AdLQuantity,AdLClearQuantity=@AdLClearQuantity,AdLStatus=@AdLStatus, ");

        //執行亂丟垃圾相關行為告發及工廁檢查統計表參數 --18
        sb.AppendLine(" TAction1Report=@TAction1Report,TAction1Exhort=@TAction1Exhort,TAction1Catch=@TAction1Catch,TAction2Report=@TAction2Report,TAction2Exhort=@TAction2Exhort,TAction3Report=@TAction3Report, ");
        sb.AppendLine(" TAction3Exhort=@TAction3Exhort,TAction4Report=@TAction4Report,TAction4Exhort=@TAction4Exhort,TAction5Report=@TAction5Report,TAction5Exhort=@TAction5Exhort,TAction6Quantity=@TAction6Quantity, ");
        sb.AppendLine(" TAction7Report=@TAction7Report,TAction7Exhort=@TAction7Exhort,TAction8Report=@TAction8Report,TAction8Exhort=@TAction8Exhort,TAction8Ser=@TAction8Ser,TStatus=@TStatus, ");

        //巨大垃圾回收再利用狀況參數 --43
        sb.AppendLine(" BTfurniture1=@BTfurniture1,BTfurniture2=@BTfurniture2,BTfurniture3=@BTfurniture3,BTfurniture4=@BTfurniture4,BTfurniture5=@BTfurniture5,BTfurniture6=@BTfurniture6, ");
        sb.AppendLine(" BTsofa1=@BTsofa1,BTsofa2=@BTsofa2,BTsofa3=@BTsofa3,BTsofa4=@BTsofa4,BTsofa5=@BTsofa5,BTsofa6=@BTsofa6, ");
        sb.AppendLine(" BTbed1=@BTbed1,BTbed2=@BTbed2,BTbed3=@BTbed3,BTbed4=@BTbed4,BTbed5=@BTbed5,BTbed6=@BTbed6, ");
        sb.AppendLine(" BTbicycle1=@BTbicycle1,BTbicycle2=@BTbicycle2,BTbicycle3=@BTbicycle3,BTbicycle4=@BTbicycle4,BTbicycle5=@BTbicycle5,BTbicycle6=@BTbicycle6, ");
        sb.AppendLine(" BTtree1=@BTtree1,BTtree2=@BTtree2,BTtree3=@BTtree3,BTtree4=@BTtree4,BTtree5=@BTtree5,BTtree6=@BTtree6, ");
        sb.AppendLine(" BTother1=@BTother1,BTother2=@BTother2,BTother3=@BTother3,BTother4=@BTother4,BTother5=@BTother5,BTother6=@BTother6, ");
        sb.AppendLine(" BTaccount1=@BTaccount1,BTaccount2=@BTaccount2,BTaccount3=@BTaccount3,BTaccount4=@BTaccount4,BTaccount5=@BTaccount5,BTaccount6=@BTaccount6, ");
        sb.AppendLine(" BTStatus=@BTStatus, ");

        //廢棄物污染源稽查處分概況參數 --20
        sb.AppendLine(" ACInEmp=@ACInEmp,ACOutEmp=@ACOutEmp,ACSerNum=@ACSerNum,ACFineNum=@ACFineNum,ACHaveFineNum=@ACHaveFineNum,ACFineAccount=@ACFineAccount, ");
        sb.AppendLine(" ACComplainAccount=@ACComplainAccount,ACUnitAccount=@ACUnitAccount,ACVirtualMoney=@ACVirtualMoney,ACRealMoney=@ACRealMoney,ACTotalNum=@ACTotalNum,ACTotalMoney=@ACTotalMoney, ");
        sb.AppendLine(" ACPass1Num=@ACPass1Num,ACPass2Num=@ACPass2Num,ACPass2Money=@ACPass2Money,ACCompleteNum=@ACCompleteNum,ACCompleteMoney=@ACCompleteMoney,ACCertificateNum=@ACCertificateNum, ");
        sb.AppendLine(" ACCertificateMoney=@ACCertificateMoney,ACStatus=@ACStatus,  ");

        //處理情形說明
        sb.AppendLine(" State=@State ");

        sb.AppendLine("Where OrganizationID = @OrganizationID ");
        sb.AppendLine("And Year = @Year ");
        sb.AppendLine("and Month = @Month ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);
    }


    //查詢公所數據在月份區間的總和
    public DataTable FD_MonthForm_SelectSum(String OrganizationID, String Year, String Month1, String Month2, String Month3, String Month4,
                   String Month5, String Month6, String Month7, String Month8, String Month9, String Month10, String Month11, String Month12)
    {
        SqlParameter[] sqlParams = new SqlParameter[14];

        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.Int);
        sqlParams[0].Value = OrganizationID;
        sqlParams[1] = new SqlParameter("@Year", SqlDbType.Int);
        sqlParams[1].Value = Year;
        sqlParams[2] = new SqlParameter("@Month1", SqlDbType.Int);
        sqlParams[2].Value = Month1;
        sqlParams[3] = new SqlParameter("@Month2", SqlDbType.Int);
        sqlParams[3].Value = Month2;
        sqlParams[4] = new SqlParameter("@Month3", SqlDbType.Int);
        sqlParams[4].Value = Month3;
        sqlParams[5] = new SqlParameter("@Month4", SqlDbType.Int);
        sqlParams[5].Value = Month4;
        sqlParams[6] = new SqlParameter("@Month5", SqlDbType.Int);
        sqlParams[6].Value = Month5;
        sqlParams[7] = new SqlParameter("@Month6", SqlDbType.Int);
        sqlParams[7].Value = Month6;
        sqlParams[8] = new SqlParameter("@Month7", SqlDbType.Int);
        sqlParams[8].Value = Month7;
        sqlParams[9] = new SqlParameter("@Month8", SqlDbType.Int);
        sqlParams[9].Value = Month8;
        sqlParams[10] = new SqlParameter("@Month9", SqlDbType.Int);
        sqlParams[10].Value = Month9;
        sqlParams[11] = new SqlParameter("@Month10", SqlDbType.Int);
        sqlParams[11].Value = Month10;
        sqlParams[12] = new SqlParameter("@Month11", SqlDbType.Int);
        sqlParams[12].Value = Month11;
        sqlParams[13] = new SqlParameter("@Month12", SqlDbType.Int);
        sqlParams[13].Value = Month12;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Select sum(DengueClear1) as DengueClear1,sum(DengueClear2) as DengueClear2,sum(DengueClear3), ");
        sb.AppendLine(" sum(DengueClear4),sum(DengueClear5),sum(DengueClear6),sum(DengueClear7),sum(DengueClear8), ");
        sb.AppendLine(" sum(DengueClear9),sum(DenguePublic1),sum(DenguePublic2),sum(DenguePublic3),sum(DenguePublic4), ");
        sb.AppendLine(" sum(DenguePublic5),sum(DenguePublic6),sum(DengueMedication1),sum(DengueMedication2),sum(DengueSer1), ");
        sb.AppendLine(" sum(DengueSer2),sum(DengueSer3),sum(DengueSer4),sum(DitchMember),sum(DitchDate), ");
        sb.AppendLine(" sum(DitchFutureLength),sum(DitchRealLength),sum(FilthQuantity),sum(AdSQuantity),sum(AdSClearQuantity), ");
        sb.AppendLine(" sum(AdSPeople),sum(AdLQuantity),sum(AdLClearQuantity),sum(TAction1Report),sum(TAction1Exhort), ");
        sb.AppendLine(" sum(TAction1Catch),sum(TAction2Report),sum(TAction2Exhort),sum(TAction3Report),sum(TAction3Exhort), ");
        sb.AppendLine(" sum(TAction4Report),sum(TAction4Exhort),sum(TAction5Report),sum(TAction5Exhort),sum(TAction6Quantity), ");
        sb.AppendLine(" sum(TAction7Report),sum(TAction7Exhort),sum(TAction8Report),sum(TAction8Exhort),sum(TAction8Ser), ");
        sb.AppendLine(" sum(BTfurniture1),sum(BTfurniture2),sum(BTfurniture3),sum(BTfurniture4),sum(BTfurniture5), ");
        sb.AppendLine(" sum(BTfurniture6),sum(BTsofa1),sum(BTsofa2),sum(BTsofa3),sum(BTsofa4), ");
        sb.AppendLine(" sum(BTsofa5),sum(BTsofa6),sum(BTbed1),sum(BTbed2),sum(BTbed3), ");
        sb.AppendLine(" sum(BTbed4),sum(BTbed5),sum(BTbed6),sum(BTbicycle1),sum(BTbicycle2), ");
        sb.AppendLine(" sum(BTbicycle3),sum(BTbicycle4),sum(BTbicycle5),sum(BTbicycle6),sum(BTtree1), ");
        sb.AppendLine(" sum(BTtree2),sum(BTtree3),sum(BTtree4),sum(BTtree5),sum(BTtree6), ");
        sb.AppendLine(" sum(BTother1),sum(BTother2),sum(BTother3),sum(BTother4),sum(BTother5), ");
        sb.AppendLine(" sum(BTother6),sum(BTaccount1),sum(BTaccount2),sum(BTaccount3),sum(BTaccount4), ");
        sb.AppendLine(" sum(BTaccount5),sum(BTaccount6),sum(ACInEmp),sum(ACOutEmp),sum(ACSerNum), ");
        sb.AppendLine(" sum(ACFineNum),sum(ACHaveFineNum),sum(ACFineAccount),sum(ACComplainAccount),sum(ACUnitAccount), ");
        sb.AppendLine(" sum(ACVirtualMoney),sum(ACRealMoney),sum(ACTotalNum),sum(ACTotalMoney),sum(ACPass1Num), ");
        sb.AppendLine(" sum(ACPass2Num),sum(ACPass2Money),sum(ACCompleteNum),sum(ACCompleteMoney),sum(ACCertificateNum),sum(ACCertificateMoney) ");
        sb.AppendLine(" from FD_MonthForm ");
        sb.AppendLine(" where Year = @Year ");
        sb.AppendLine(" and month in (@Month1,@Month2,@Month3,@Month4,@Month5,@Month6,@Month7,@Month8,@Month9,@Month10,@Month11,@Month12) ");
        sb.AppendLine(" and OrganizationID=@OrganizationID ");
        sb.AppendLine(" group by OrganizationID,Year ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        if (DS.Tables[0].Rows.Count == 0)
            return null;
        else
            return DS.Tables[0];
    }


    //查詢公所某項目在月份區間的數據資料
    public DataTable FD_MonthForm_SelectByMonth(String OrganizationID, String Item, String Year, String Month)
    {
        SqlParameter[] sqlParams = new SqlParameter[3];

        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.Int);
        sqlParams[0].Value = OrganizationID;
        sqlParams[1] = new SqlParameter("@Year", SqlDbType.Int);
        sqlParams[1].Value = Year;
        sqlParams[2] = new SqlParameter("@Month", SqlDbType.Int);
        sqlParams[2].Value = Month;


        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Select " + Item + " From FD_MonthForm ");
        sb.AppendLine(" Where OrganizationID = @OrganizationID ");
        sb.AppendLine(" And [Month] = @Month ");
        sb.AppendLine(" And [Year] = @Year ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        return DS.Tables[0];
    }

    //查詢主項目，次項目，子項目的名稱
    public DataTable FD_MonthForm_SelectItemName()
    {
        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Select a.Name as FirstItem,b.Name as SecondItem,c.Name as ThirdItem,c.Value,c.itemid ");
        sb.AppendLine(" From FD_Report_Master a inner join FD_Report_Item b ");
        sb.AppendLine(" On a.id = b.MasterID ");
        sb.AppendLine(" Inner Join FD_Report_SubItem c ");
        sb.AppendLine(" On b.id = c.itemID ");
        sb.AppendLine(" Order By c.itemid ,c.id ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString());

        return DS.Tables[0];
    }

    //依 Value 查詢主項目，次項目，子項目的名稱
    public DataTable FD_MonthForm_SelectItemByValue(String Value)
    {
        SqlParameter[] sqlParams = new SqlParameter[1];

        sqlParams[0] = new SqlParameter("@Value", SqlDbType.NVarChar, 50);
        sqlParams[0].Value = Value;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Select a.Name as FirstItem,b.Name as SecondItem,c.Name as ThirdItem,c.Value ");
        sb.AppendLine(" From FD_Report_Master a inner join FD_Report_Item b ");
        sb.AppendLine(" On a.id = b.MasterID ");
        sb.AppendLine(" inner join FD_Report_SubItem c ");
        sb.AppendLine(" on b.id = c.itemID ");
        sb.AppendLine(" where c.Value = @Value ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        return DS.Tables[0];
    }

    //鄉公所月報表填寫時間查詢
    public DataTable FD_SetMonthFormDate_Select()
    {
        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Select * From FD_SetMonthFormDate ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString());

        return DS.Tables[0];
    }

    //鄉公所月報表填寫時間更新
    public int FD_SetMonthFormDate_Update(String DateStart, String DateEnd)
    {
        SqlParameter[] sqlParams = new SqlParameter[2];

        sqlParams[0] = new SqlParameter("@DateStart", SqlDbType.Int);
        sqlParams[0].Value = DateStart;
        sqlParams[1] = new SqlParameter("@DateEnd", SqlDbType.Int);
        sqlParams[1].Value = DateEnd;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Update FD_SetMonthFormDate ");
        sb.AppendLine(" Set DateStart = @DateStart, ");
        sb.AppendLine(" DateEnd = @DateEnd ");
        sb.AppendLine(" where id='1' ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);

    }

    //鄉公所資料狀態更新
    public int FD_MonthFormStatus_Update(String OrganizationID, String Item, String Year, String Month, String Status)
    {
        SqlParameter[] sqlParams = new SqlParameter[4];

        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.Int);
        sqlParams[0].Value = OrganizationID;
        sqlParams[1] = new SqlParameter("@Year", SqlDbType.Int);
        sqlParams[1].Value = Year;
        sqlParams[2] = new SqlParameter("@Month", SqlDbType.Int);
        sqlParams[2].Value = Month;
        sqlParams[3] = new SqlParameter("@Status", SqlDbType.NChar, 1);
        sqlParams[3].Value = Status;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Update FD_MonthForm ");
        sb.AppendLine(" Set " + Item + "= @Status ");
        sb.AppendLine(" where OrganizationID = @OrganizationID ");
        sb.AppendLine(" and [Year] = @Year ");
        sb.AppendLine(" and [Month] = @Month ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);

    }

    //查詢公所月報表錯誤的項目及月份
    public DataTable FD_MonthFormErrorStatus_Select(String OrganizationID)
    {
        SqlParameter[] sqlParams = new SqlParameter[1];

        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.Int);
        sqlParams[0].Value = OrganizationID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Select Year,Month,DengueStatus,DitchStatus,FilthStatus, ");
        sb.AppendLine(" AdStatus,AdLStatus,TStatus,BTStatus,ACStatus ");
        sb.AppendLine(" From FD_MonthForm ");
        sb.AppendLine(" Where OrganizationID=@OrganizationID ");
        sb.AppendLine(" And (DengueStatus='N' or DitchStatus='N' or FilthStatus='N' ");
        sb.AppendLine(" or AdStatus='N' or AdLStatus='N' or TStatus='N' ");
        sb.AppendLine(" or BTStatus='N' or ACStatus='N') ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        return DS.Tables[0];
    }

    //判斷是否在填寫月報表的時間區間
    public DataTable FD_SetMonthFormDates_Select(String Date)
    {
        SqlParameter[] sqlParams = new SqlParameter[1];

        sqlParams[0] = new SqlParameter("@Date", SqlDbType.NVarChar, 3);
        sqlParams[0].Value = Date;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Select DateStart,DateEnd ");
        sb.AppendLine(" From FD_SetMonthFormDate ");
        sb.AppendLine(" Where DateStart < ('" + @Date + "'+1) and DateEnd > ('" + @Date + "'-1) ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        return DS.Tables[0];
    }

    //查詢鄉公所未更正的錯誤資料
    public DataTable FD_MonthFormError_Select(String OrganizationID, String Year, String Month)
    {
        SqlParameter[] sqlParams = new SqlParameter[3];

        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.Int);
        sqlParams[0].Value = OrganizationID;
        sqlParams[1] = new SqlParameter("@Year", SqlDbType.Int);
        sqlParams[1].Value = Year;
        sqlParams[2] = new SqlParameter("@Month", SqlDbType.Int);
        sqlParams[2].Value = Month;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Select * from FD_MonthFormError ");
        sb.AppendLine(" Where OrganizationID=@OrganizationID ");
        sb.AppendLine(" And Year = @Year ");
        sb.AppendLine(" And Month = @Month ");
        sb.AppendLine(" And IsError = 'Y' ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        return DS.Tables[0];

    }

    //查詢鄉公所的錯誤資料
    public DataTable FD_MonthFormError_Select1(String OrganizationID, String Year, String Month)
    {
        SqlParameter[] sqlParams = new SqlParameter[3];

        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.Int);
        sqlParams[0].Value = OrganizationID;
        sqlParams[1] = new SqlParameter("@Year", SqlDbType.Int);
        sqlParams[1].Value = Year;
        sqlParams[2] = new SqlParameter("@Month", SqlDbType.Int);
        sqlParams[2].Value = Month;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Select * from FD_MonthFormError ");
        sb.AppendLine(" Where OrganizationID=@OrganizationID ");
        sb.AppendLine(" And Year = @Year ");
        sb.AppendLine(" And Month = @Month ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        return DS.Tables[0];

    }

    //查詢某鄉公所的所有錯誤資料
    public DataTable FD_MonthFormError_Select2(String OrganizationID)
    {
        SqlParameter[] sqlParams = new SqlParameter[1];

        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.Int);
        sqlParams[0].Value = OrganizationID;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Select * from FD_MonthFormError ");
        sb.AppendLine(" Where OrganizationID=@OrganizationID ");
        sb.AppendLine(" And IsError = 'Y' ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        return DS.Tables[0];

    }

    public DataTable FD_MonthFormError_Select3()
    {
        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Select b.OrganizationName,a.* ");
        sb.AppendLine(" From FD_MonthFormError a Inner Join Organization b ");
        sb.AppendLine(" on a.OrganizationID = b.OrganizationID ");
        sb.AppendLine(" Where a.ErrorDate <> a.UpdateDate ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString());

        return DS.Tables[0];
    }

    //新增鄉公所的錯誤資料
    public int FD_MonthFormError_Insert(String OrganizationID, String Year, String Month, String IsError, String ErrorItem
                                                , String Editor, String ErrorDate, String UpdateDate, String State)
    {
        SqlParameter[] sqlParams = new SqlParameter[9];

        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.NVarChar);
        sqlParams[0].Value = OrganizationID;
        sqlParams[1] = new SqlParameter("@Year", SqlDbType.NVarChar);
        sqlParams[1].Value = Year;
        sqlParams[2] = new SqlParameter("@Month", SqlDbType.NVarChar);
        sqlParams[2].Value = Month;
        sqlParams[3] = new SqlParameter("@IsError", SqlDbType.NVarChar);
        sqlParams[3].Value = IsError;
        sqlParams[4] = new SqlParameter("@ErrorItem", SqlDbType.NVarChar);
        sqlParams[4].Value = ErrorItem;
        sqlParams[5] = new SqlParameter("@Editor", SqlDbType.NVarChar);
        sqlParams[5].Value = Editor;
        sqlParams[6] = new SqlParameter("@ErrorDate", SqlDbType.NVarChar);
        sqlParams[6].Value = ErrorDate;
        sqlParams[7] = new SqlParameter("@UpdateDate", SqlDbType.NVarChar);
        sqlParams[7].Value = UpdateDate;
        sqlParams[8] = new SqlParameter("@State", SqlDbType.NVarChar);
        sqlParams[8].Value = State;


        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Insert Into FD_MonthFormError ");
        sb.AppendLine(" Values(@OrganizationID,@Year,@Month,@IsError,@ErrorItem,@Editor,@ErrorDate,@UpdateDate,@State) ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);

    }

    public int FD_MonthFormError_Update(String OrganizationID, String Year, String Month, String IsError, String ErrorItem
                                                , String Editor, String ErrorDate, String UpdateDate, String State)
    {
        SqlParameter[] sqlParams = new SqlParameter[9];

        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.NVarChar);
        sqlParams[0].Value = OrganizationID;
        sqlParams[1] = new SqlParameter("@Year", SqlDbType.NVarChar);
        sqlParams[1].Value = Year;
        sqlParams[2] = new SqlParameter("@Month", SqlDbType.NVarChar);
        sqlParams[2].Value = Month;
        sqlParams[3] = new SqlParameter("@IsError", SqlDbType.NVarChar);
        sqlParams[3].Value = IsError;
        sqlParams[4] = new SqlParameter("@ErrorItem", SqlDbType.NVarChar);
        sqlParams[4].Value = ErrorItem;
        sqlParams[5] = new SqlParameter("@Editor", SqlDbType.NVarChar);
        sqlParams[5].Value = Editor;
        sqlParams[6] = new SqlParameter("@ErrorDate", SqlDbType.NVarChar);
        sqlParams[6].Value = ErrorDate;
        sqlParams[7] = new SqlParameter("@UpdateDate", SqlDbType.NVarChar);
        sqlParams[7].Value = UpdateDate;
        sqlParams[8] = new SqlParameter("@State", SqlDbType.NVarChar);
        sqlParams[8].Value = State;


        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Update FD_MonthFormError ");
        sb.AppendLine(" Set IsError = @IsError, ");
        sb.AppendLine(" ErrorItem = @ErrorItem, ");
        sb.AppendLine(" Editor = @Editor, ");
        sb.AppendLine(" ErrorDate = @ErrorDate, ");
        sb.AppendLine(" [State] = @State ");
        sb.AppendLine(" Where OrganizationID = @OrganizationID ");
        sb.AppendLine(" And Year = @Year ");
        sb.AppendLine(" And Month = @Month ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);

    }


    public int FD_MonthFormError_Update1(String OrganizationID, String Year, String Month)
    {
        SqlParameter[] sqlParams = new SqlParameter[3];

        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.NVarChar);
        sqlParams[0].Value = OrganizationID;
        sqlParams[1] = new SqlParameter("@Year", SqlDbType.NVarChar);
        sqlParams[1].Value = Year;
        sqlParams[2] = new SqlParameter("@Month", SqlDbType.NVarChar);
        sqlParams[2].Value = Month;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Update FD_MonthFormError  ");
        sb.AppendLine(" Set ErrorDate = UpdateDate ");
        sb.AppendLine(" Where OrganizationID = @OrganizationID ");
        sb.AppendLine(" And Year = @Year ");
        sb.AppendLine(" And Month = @Month ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);
    }

    //更新鄉公所的錯誤資料
    public int FD_MonthFormError_Update1(String OrganizationID, String Year, String Month, String IsError, String ErrorItem
                                                , String Editor, String UpdateDate, String State)
    {
        SqlParameter[] sqlParams = new SqlParameter[8];

        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.NVarChar);
        sqlParams[0].Value = OrganizationID;
        sqlParams[1] = new SqlParameter("@Year", SqlDbType.NVarChar);
        sqlParams[1].Value = Year;
        sqlParams[2] = new SqlParameter("@Month", SqlDbType.NVarChar);
        sqlParams[2].Value = Month;
        sqlParams[3] = new SqlParameter("@IsError", SqlDbType.NVarChar);
        sqlParams[3].Value = IsError;
        sqlParams[4] = new SqlParameter("@ErrorItem", SqlDbType.NVarChar);
        sqlParams[4].Value = ErrorItem;
        sqlParams[5] = new SqlParameter("@Editor", SqlDbType.NVarChar);
        sqlParams[5].Value = Editor;
        sqlParams[6] = new SqlParameter("@UpdateDate", SqlDbType.NVarChar);
        sqlParams[6].Value = UpdateDate;
        sqlParams[7] = new SqlParameter("@State", SqlDbType.NVarChar);
        sqlParams[7].Value = State;


        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Update FD_MonthFormError ");
        sb.AppendLine(" Set IsError = @IsError, ");
        sb.AppendLine(" ErrorItem = @ErrorItem, ");
        sb.AppendLine(" Editor = @Editor, ");
        sb.AppendLine(" UpdateDate = @UpdateDate, ");
        sb.AppendLine(" [State] = @State ");
        sb.AppendLine(" Where OrganizationID = @OrganizationID ");
        sb.AppendLine(" And Year = @Year ");
        sb.AppendLine(" And Month = @Month ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);

    }
    //更新 FD_MonthFormError 的 State
    public int FD_MonthFormErrorState_Update(String OrganizationID, String Year, String Month, String State)
    {
        SqlParameter[] sqlParams = new SqlParameter[4];

        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.NVarChar);
        sqlParams[0].Value = OrganizationID;
        sqlParams[1] = new SqlParameter("@Year", SqlDbType.NVarChar);
        sqlParams[1].Value = Year;
        sqlParams[2] = new SqlParameter("@Month", SqlDbType.NVarChar);
        sqlParams[2].Value = Month;
        sqlParams[3] = new SqlParameter("@State", SqlDbType.NVarChar);
        sqlParams[3].Value = State;


        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Update FD_MonthFormError ");
        sb.AppendLine(" Set [State] = @State ");
        sb.AppendLine(" Where OrganizationID = @OrganizationID ");
        sb.AppendLine(" And Year = @Year ");
        sb.AppendLine(" And Month = @Month ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);
    }

    //新增到 MonthFormOverTime
    public int FD_MonthFormOverTime_Insert(String OrganizationID, String Year, String Month, String Status)
    {
        SqlParameter[] sqlParams = new SqlParameter[4];

        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.NVarChar);
        sqlParams[0].Value = OrganizationID;
        sqlParams[1] = new SqlParameter("@Year", SqlDbType.NVarChar);
        sqlParams[1].Value = Year;
        sqlParams[2] = new SqlParameter("@Month", SqlDbType.NVarChar);
        sqlParams[2].Value = Month;
        sqlParams[3] = new SqlParameter("@Status", SqlDbType.NVarChar);
        sqlParams[3].Value = Status;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Insert Into FD_MonthFormOverTime ");
        sb.AppendLine(" Values(@OrganizationID,@Year,@Month,@Status) ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);


    }

    //更新到 MonthFormOverTime
    public int FD_MonthFormOverTime_Update(String OrganizationID, String Year, String Month, String Status)
    {
        SqlParameter[] sqlParams = new SqlParameter[4];

        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.NVarChar);
        sqlParams[0].Value = OrganizationID;
        sqlParams[1] = new SqlParameter("@Year", SqlDbType.NVarChar);
        sqlParams[1].Value = Year;
        sqlParams[2] = new SqlParameter("@Month", SqlDbType.NVarChar);
        sqlParams[2].Value = Month;
        sqlParams[3] = new SqlParameter("@Status", SqlDbType.NVarChar);
        sqlParams[3].Value = Status;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Update FD_MonthFormOverTime ");
        sb.AppendLine(" Set Status = @Status ");
        sb.AppendLine(" Where OrganizationID = @OrganizationID ");
        sb.AppendLine(" And Year = @Year ");
        sb.AppendLine(" And Month = @Month ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);


    }

    //查詢 MonthFormOverTime
    public DataTable FD_MonthFormOverTime_Select(String OrganizationID, String Year, String Month)
    {
        SqlParameter[] sqlParams = new SqlParameter[3];

        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.NVarChar);
        sqlParams[0].Value = OrganizationID;
        sqlParams[1] = new SqlParameter("@Year", SqlDbType.NVarChar);
        sqlParams[1].Value = Year;
        sqlParams[2] = new SqlParameter("@Month", SqlDbType.NVarChar);
        sqlParams[2].Value = Month;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Select * From FD_MonthFormOverTime ");
        sb.AppendLine(" Where OrganizationID = @OrganizationID ");
        sb.AppendLine(" And Year = @Year ");
        sb.AppendLine(" And Month = @Month ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        return DS.Tables[0];

    }

    public int FD_MonthFormOverTime_Delete(String OrganizationID, String Year, String Month)
    {
        SqlParameter[] sqlParams = new SqlParameter[3];

        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.NVarChar);
        sqlParams[0].Value = OrganizationID;
        sqlParams[1] = new SqlParameter("@Year", SqlDbType.NVarChar);
        sqlParams[1].Value = Year;
        sqlParams[2] = new SqlParameter("@Month", SqlDbType.NVarChar);
        sqlParams[2].Value = Month;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Delete from FD_MonthFormOverTime  ");
        sb.AppendLine(" Where OrganizationID = @OrganizationID ");
        sb.AppendLine(" And Year = @Year ");
        sb.AppendLine(" And Month = @Month ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);
    }

    //新增到 MonthFormInTime
    public int FD_MonthFormInTime_Insert(String OrganizationID, String Year, String Month, String Status)
    {
        SqlParameter[] sqlParams = new SqlParameter[4];

        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.NVarChar);
        sqlParams[0].Value = OrganizationID;
        sqlParams[1] = new SqlParameter("@Year", SqlDbType.NVarChar);
        sqlParams[1].Value = Year;
        sqlParams[2] = new SqlParameter("@Month", SqlDbType.NVarChar);
        sqlParams[2].Value = Month;
        sqlParams[3] = new SqlParameter("@Status", SqlDbType.NVarChar);
        sqlParams[3].Value = Status;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Insert Into FD_MonthFormInTime ");
        sb.AppendLine(" Values(@OrganizationID,@Year,@Month,@Status) ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);


    }

    //更新到 MonthFormInTime
    public int FD_MonthFormInTime_Update(String OrganizationID, String Year, String Month, String Status)
    {
        SqlParameter[] sqlParams = new SqlParameter[4];

        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.NVarChar);
        sqlParams[0].Value = OrganizationID;
        sqlParams[1] = new SqlParameter("@Year", SqlDbType.NVarChar);
        sqlParams[1].Value = Year;
        sqlParams[2] = new SqlParameter("@Month", SqlDbType.NVarChar);
        sqlParams[2].Value = Month;
        sqlParams[3] = new SqlParameter("@Status", SqlDbType.NVarChar);
        sqlParams[3].Value = Status;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Update FD_MonthFormInTime ");
        sb.AppendLine(" Set Sattus = @Status) ");
        sb.AppendLine(" Where OrganizationID = @OrganizationID ");
        sb.AppendLine(" And Year = @Year ");
        sb.AppendLine(" And Month = @Month ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);


    }

    //查詢 MonthFormInTime
    public DataTable FD_MonthFormInTime_Select(String OrganizationID, String Year, String Month)
    {
        SqlParameter[] sqlParams = new SqlParameter[3];

        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.NVarChar);
        sqlParams[0].Value = OrganizationID;
        sqlParams[1] = new SqlParameter("@Year", SqlDbType.NVarChar);
        sqlParams[1].Value = Year;
        sqlParams[2] = new SqlParameter("@Month", SqlDbType.NVarChar);
        sqlParams[2].Value = Month;

        StringBuilder sb = new StringBuilder();

        sb.AppendLine(" Select * From FD_MonthFormInTime ");
        sb.AppendLine(" Where OrganizationID = @OrganizationID ");
        sb.AppendLine(" And Year = @Year ");
        sb.AppendLine(" And Month = @Month ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        return DS.Tables[0];

    }

    //查詢公所某月的登革熱病媒蚊防治資料
    public DataTable FD_DengueForm_Select(String OrganizationID, String Year, String Month)
    {
        SqlParameter[] sqlParams = new SqlParameter[3];

        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.Int);
        sqlParams[0].Value = OrganizationID;
        sqlParams[1] = new SqlParameter("@Year", SqlDbType.Int);
        sqlParams[1].Value = Year;
        sqlParams[2] = new SqlParameter("@Month", SqlDbType.Int);
        sqlParams[2].Value = Month;

        StringBuilder sb = new StringBuilder();

        //辦理登革熱病媒蚊防治工作紀錄參數  --33
        sb.AppendLine(" Select * ");
        sb.AppendLine(" From FD_MonthForm_Dengue ");
        sb.AppendLine(" Where OrganizationID = @OrganizationID ");
        sb.AppendLine(" And [Year] = @Year ");
        sb.AppendLine(" And [Month] = @Month ");

        DataSet DS = SqlHelper.ExecuteDataset(conn, CommandType.Text, sb.ToString(), sqlParams);

        return DS.Tables[0];
    }


    //新增公所某月的登革熱病媒蚊防治資料
    public int FD_DengueForm_Insert(String OrganizationID, String Year, String Month, String DengueClear1,
        String DengueClear2, String DengueClear3, String DengueClear4, String DengueClear5, String DengueClear6,
        String DengueClear7, String DengueClear8, String DengueClear9, String DengueClearDate, String DengueClearPlace,
        String DenguePublic1, String DenguePublic2, String DenguePublic3, String DenguePublic4, String DenguePublic5,
        String DenguePublic6, String DenguePublicDate, String DenguePublicPlace, String DengueMedication1,
        String DengueMedication2, String DengueMedicationDate, String DengueMedicationPlace, String DengueSer1,
        String DengueSer2, String DengueSer3, String DengueSer4, String DengueSerDate, String DengueSerPlace, String DengueState)
    {
        SqlParameter[] sqlParams = new SqlParameter[33];

        //辦理登革熱病媒蚊防治工作紀錄
        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.Int);
        sqlParams[0].Value = OrganizationID;
        sqlParams[1] = new SqlParameter("@Year", SqlDbType.Int);
        sqlParams[1].Value = Year;
        sqlParams[2] = new SqlParameter("@Month", SqlDbType.Int);
        sqlParams[2].Value = Month;
        sqlParams[3] = new SqlParameter("@DengueClear1", SqlDbType.Int);
        sqlParams[3].Value = DengueClear1;
        sqlParams[4] = new SqlParameter("@DengueClear2", SqlDbType.Int);
        sqlParams[4].Value = DengueClear2;
        sqlParams[5] = new SqlParameter("@DengueClear3", SqlDbType.Int);
        sqlParams[5].Value = DengueClear3;
        sqlParams[6] = new SqlParameter("@DengueClear4", SqlDbType.Int);
        sqlParams[6].Value = DengueClear4;
        sqlParams[7] = new SqlParameter("@DengueClear5", SqlDbType.Int);
        sqlParams[7].Value = DengueClear5;
        sqlParams[8] = new SqlParameter("@DengueClear6", SqlDbType.Int);
        sqlParams[8].Value = DengueClear6;
        sqlParams[9] = new SqlParameter("@DengueClear7", SqlDbType.Int);
        sqlParams[9].Value = DengueClear7;
        sqlParams[10] = new SqlParameter("@DengueClear8", SqlDbType.Int);
        sqlParams[10].Value = DengueClear8;
        sqlParams[11] = new SqlParameter("@DengueClear9", SqlDbType.Int);
        sqlParams[11].Value = DengueClear9;
        sqlParams[12] = new SqlParameter("@DengueClearDate", SqlDbType.DateTime);
        sqlParams[12].Value = DengueClearDate;
        sqlParams[13] = new SqlParameter("@DengueClearPlace", SqlDbType.NVarChar, 50);
        sqlParams[13].Value = DengueClearPlace;
        sqlParams[14] = new SqlParameter("@DenguePublic1", SqlDbType.Int);
        sqlParams[14].Value = DenguePublic1;
        sqlParams[15] = new SqlParameter("@DenguePublic2", SqlDbType.Int);
        sqlParams[15].Value = DenguePublic2;
        sqlParams[16] = new SqlParameter("@DenguePublic3", SqlDbType.Int);
        sqlParams[16].Value = DenguePublic3;
        sqlParams[17] = new SqlParameter("@DenguePublic4", SqlDbType.Int);
        sqlParams[17].Value = DenguePublic4;
        sqlParams[18] = new SqlParameter("@DenguePublic5", SqlDbType.Int);
        sqlParams[18].Value = DenguePublic5;
        sqlParams[19] = new SqlParameter("@DenguePublic6", SqlDbType.Int);
        sqlParams[19].Value = DenguePublic6;
        sqlParams[20] = new SqlParameter("@DenguePublicDate", SqlDbType.DateTime);
        sqlParams[20].Value = DenguePublicDate;
        sqlParams[21] = new SqlParameter("@DenguePublicPlace", SqlDbType.NVarChar, 50);
        sqlParams[21].Value = DenguePublicPlace;
        sqlParams[22] = new SqlParameter("@DengueMedication1", SqlDbType.Int);
        sqlParams[22].Value = DengueMedication1;
        sqlParams[23] = new SqlParameter("@DengueMedication2", SqlDbType.Int);
        sqlParams[23].Value = DengueMedication2;
        sqlParams[24] = new SqlParameter("@DengueMedicationDate", SqlDbType.DateTime);
        sqlParams[24].Value = DengueMedicationDate;
        sqlParams[25] = new SqlParameter("@DengueMedicationPlace", SqlDbType.NVarChar, 50);
        sqlParams[25].Value = DengueMedicationPlace;
        sqlParams[26] = new SqlParameter("@DengueSer1", SqlDbType.Int);
        sqlParams[26].Value = DengueSer1;
        sqlParams[27] = new SqlParameter("@DengueSer2", SqlDbType.Int);
        sqlParams[27].Value = DengueSer2;
        sqlParams[28] = new SqlParameter("@DengueSer3", SqlDbType.Int);
        sqlParams[28].Value = DengueSer3;
        sqlParams[29] = new SqlParameter("@DengueSer4", SqlDbType.Int);
        sqlParams[29].Value = DengueSer4;
        sqlParams[30] = new SqlParameter("@DengueSerDate", SqlDbType.DateTime);
        sqlParams[30].Value = DengueSerDate;
        sqlParams[31] = new SqlParameter("@DengueSerPlace", SqlDbType.NVarChar, 50);
        sqlParams[31].Value = DengueSerPlace;
        sqlParams[32] = new SqlParameter("@DengueState", SqlDbType.NVarChar, 200);
        sqlParams[32].Value = DengueState;


        StringBuilder sb = new StringBuilder();

        //辦理登革熱病媒蚊防治工作紀錄參數  --33
        sb.AppendLine(" Insert Into FD_MonthForm_Dengue ");
        sb.AppendLine(" Values(@OrganizationID,@Year,@Month,@DengueClear1,@DengueClear2,@DengueClear3, ");
        sb.AppendLine(" @DengueClear4,@DengueClear5,@DengueClear6,@DengueClear7,@DengueClear8,@DengueClear9, ");
        sb.AppendLine(" @DengueClearDate,@DengueClearPlace,@DenguePublic1,@DenguePublic2,@DenguePublic3,@DenguePublic4, ");
        sb.AppendLine(" @DenguePublic5,@DenguePublic6,@DenguePublicDate,@DenguePublicPlace,@DengueMedication1,@DengueMedication2, ");
        sb.AppendLine(" @DengueMedicationDate,@DengueMedicationPlace,@DengueSer1,@DengueSer2,@DengueSer3,@DengueSer4, ");
        sb.AppendLine(" @DengueSerDate,@DengueSerPlace,@DengueState) ");


        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);


    }

    //更新公所某月的登革熱病媒蚊防治資料
    public int FD_DengueForm_Update(String OrganizationID, String Year, String Month, String DengueClear1,
        String DengueClear2, String DengueClear3, String DengueClear4, String DengueClear5, String DengueClear6,
        String DengueClear7, String DengueClear8, String DengueClear9, String DengueClearDate, String DengueClearPlace,
        String DenguePublic1, String DenguePublic2, String DenguePublic3, String DenguePublic4, String DenguePublic5,
        String DenguePublic6, String DenguePublicDate, String DenguePublicPlace, String DengueMedication1,
        String DengueMedication2, String DengueMedicationDate, String DengueMedicationPlace, String DengueSer1,
        String DengueSer2, String DengueSer3, String DengueSer4, String DengueSerDate, String DengueSerPlace, String DengueState)
    {
        SqlParameter[] sqlParams = new SqlParameter[33];

        //辦理登革熱病媒蚊防治工作紀錄
        sqlParams[0] = new SqlParameter("@OrganizationID", SqlDbType.Int);
        sqlParams[0].Value = OrganizationID;
        sqlParams[1] = new SqlParameter("@Year", SqlDbType.Int);
        sqlParams[1].Value = Year;
        sqlParams[2] = new SqlParameter("@Month", SqlDbType.Int);
        sqlParams[2].Value = Month;
        sqlParams[3] = new SqlParameter("@DengueClear1", SqlDbType.Int);
        sqlParams[3].Value = DengueClear1;
        sqlParams[4] = new SqlParameter("@DengueClear2", SqlDbType.Int);
        sqlParams[4].Value = DengueClear2;
        sqlParams[5] = new SqlParameter("@DengueClear3", SqlDbType.Int);
        sqlParams[5].Value = DengueClear3;
        sqlParams[6] = new SqlParameter("@DengueClear4", SqlDbType.Int);
        sqlParams[6].Value = DengueClear4;
        sqlParams[7] = new SqlParameter("@DengueClear5", SqlDbType.Int);
        sqlParams[7].Value = DengueClear5;
        sqlParams[8] = new SqlParameter("@DengueClear6", SqlDbType.Int);
        sqlParams[8].Value = DengueClear6;
        sqlParams[9] = new SqlParameter("@DengueClear7", SqlDbType.Int);
        sqlParams[9].Value = DengueClear7;
        sqlParams[10] = new SqlParameter("@DengueClear8", SqlDbType.Int);
        sqlParams[10].Value = DengueClear8;
        sqlParams[11] = new SqlParameter("@DengueClear9", SqlDbType.Int);
        sqlParams[11].Value = DengueClear9;
        sqlParams[12] = new SqlParameter("@DengueClearDate", SqlDbType.DateTime);
        sqlParams[12].Value = DengueClearDate;
        sqlParams[13] = new SqlParameter("@DengueClearPlace", SqlDbType.NVarChar, 50);
        sqlParams[13].Value = DengueClearPlace;
        sqlParams[14] = new SqlParameter("@DenguePublic1", SqlDbType.Int);
        sqlParams[14].Value = DenguePublic1;
        sqlParams[15] = new SqlParameter("@DenguePublic2", SqlDbType.Int);
        sqlParams[15].Value = DenguePublic2;
        sqlParams[16] = new SqlParameter("@DenguePublic3", SqlDbType.Int);
        sqlParams[16].Value = DenguePublic3;
        sqlParams[17] = new SqlParameter("@DenguePublic4", SqlDbType.Int);
        sqlParams[17].Value = DenguePublic4;
        sqlParams[18] = new SqlParameter("@DenguePublic5", SqlDbType.Int);
        sqlParams[18].Value = DenguePublic5;
        sqlParams[19] = new SqlParameter("@DenguePublic6", SqlDbType.Int);
        sqlParams[19].Value = DenguePublic6;
        sqlParams[20] = new SqlParameter("@DenguePublicDate", SqlDbType.DateTime);
        sqlParams[20].Value = DenguePublicDate;
        sqlParams[21] = new SqlParameter("@DenguePublicPlace", SqlDbType.NVarChar, 50);
        sqlParams[21].Value = DenguePublicPlace;
        sqlParams[22] = new SqlParameter("@DengueMedication1", SqlDbType.Int);
        sqlParams[22].Value = DengueMedication1;
        sqlParams[23] = new SqlParameter("@DengueMedication2", SqlDbType.Int);
        sqlParams[23].Value = DengueMedication2;
        sqlParams[24] = new SqlParameter("@DengueMedicationDate", SqlDbType.DateTime);
        sqlParams[24].Value = DengueMedicationDate;
        sqlParams[25] = new SqlParameter("@DengueMedicationPlace", SqlDbType.NVarChar, 50);
        sqlParams[25].Value = DengueMedicationPlace;
        sqlParams[26] = new SqlParameter("@DengueSer1", SqlDbType.Int);
        sqlParams[26].Value = DengueSer1;
        sqlParams[27] = new SqlParameter("@DengueSer2", SqlDbType.Int);
        sqlParams[27].Value = DengueSer2;
        sqlParams[28] = new SqlParameter("@DengueSer3", SqlDbType.Int);
        sqlParams[28].Value = DengueSer3;
        sqlParams[29] = new SqlParameter("@DengueSer4", SqlDbType.Int);
        sqlParams[29].Value = DengueSer4;
        sqlParams[30] = new SqlParameter("@DengueSerDate", SqlDbType.DateTime);
        sqlParams[30].Value = DengueSerDate;
        sqlParams[31] = new SqlParameter("@DengueSerPlace", SqlDbType.NVarChar, 50);
        sqlParams[31].Value = DengueSerPlace;
        sqlParams[32] = new SqlParameter("@DengueState", SqlDbType.NVarChar, 200);
        sqlParams[32].Value = DengueState;


        StringBuilder sb = new StringBuilder();

        //辦理登革熱病媒蚊防治工作紀錄參數  --33
        sb.AppendLine(" Update FD_MonthForm_Dengue ");
        sb.AppendLine(" Set DengueClear1=@DengueClear1,DengueClear2=@DengueClear2,DengueClear3=@DengueClear3, ");
        sb.AppendLine(" DengueClear4=@DengueClear4,DengueClear5=@DengueClear5,DengueClear6=@DengueClear6,DengueClear7=@DengueClear7,DengueClear8=@DengueClear8,DengueClear9=@DengueClear9, ");
        sb.AppendLine(" DengueClearDate=@DengueClearDate,DengueClearPlace=@DengueClearPlace,DenguePublic1=@DenguePublic1,DenguePublic2=@DenguePublic2,DenguePublic3=@DenguePublic3,DenguePublic4=@DenguePublic4, ");
        sb.AppendLine(" DenguePublic5=@DenguePublic5,DenguePublic6=@DenguePublic6,DenguePublicDate=@DenguePublicDate,DenguePublicPlace=@DenguePublicPlace,DengueMedication1=@DengueMedication1,DengueMedication2=@DengueMedication2, ");
        sb.AppendLine(" DengueMedicationDate=@DengueMedicationDate,DengueMedicationPlace=@DengueMedicationPlace,DengueSer1=@DengueSer1,DengueSer2=@DengueSer2,DengueSer3=@DengueSer3,DengueSer4=@DengueSer4, ");
        sb.AppendLine(" DengueSerDate=@DengueSerDate,DengueSerPlace=@DengueSerPlace,DengueState=@DengueState ");

        sb.AppendLine("Where OrganizationID = @OrganizationID ");
        sb.AppendLine("And Year = @Year ");
        sb.AppendLine("and Month = @Month ");

        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sb.ToString(), sqlParams);
    }


    #endregion

}
